Restore SQL Server Backup to Docker Container

Restoring a SQL Server backup in a Docker container is quite easy. To begin with, you will need persistent storage on your Docker container to follow this blog post. To get that setup, you can follow the instructions in this blog post.

I found that the restore wouldn’t work without first creating an empty database. In this case, I wanted to restore a backup from Steve Stedman’s corruption challenge, so I created an empty database with that named database.

USE master; 
CREATE DATABASE [CorruptionChallenge1]
( NAME = N'CorruptionChallenge1', FILENAME = N'/var/opt/mssql/data/CorruptionChallenge1.mdf')
( NAME = N'CorruptionChallenge1_log', FILENAME = N'/var/opt/mssql/data/CorruptionChallenge1_log.ldf')

Next, make sure to put your .bak file in the persistent data folder. This way your Docker instance has access to your .bak file. For me, that is C:\docker\sql2019\data. For you, it’s wherever you mapped your persistent storage. In my case, C:\docker\sql2019\data maps to /var/opt/mssql/data/ in my Docker container.

Then you restore over the existing database.

USE [master]
RESTORE DATABASE [CorruptionChallenge1] 
FROM  DISK = N'/var/opt/mssql/data/CorruptionChallenge1.bak' 
WITH  FILE = 1,  
MOVE N'CorruptionChallenge1' TO N'/var/opt/mssql/data/CorruptionChallenge1.mdf',  
MOVE N'CorruptionChallenge1_log' TO N'/var/opt/mssql/data/CorruptionChallenge1_log.ldf',  

Then you have the restored SQL Server database in place ready to query in Docker. I restored a corrupted database because I wanted to see how the Ola index maintenance stored procedure captured any error on corrupt indexes. My next post will cover what happens when an index is corrupt when Ola runs index maintenance against it.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.