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]
 ON  PRIMARY 
( NAME = N'CorruptionChallenge1', FILENAME = N'/var/opt/mssql/data/CorruptionChallenge1.mdf')
 LOG ON 
( NAME = N'CorruptionChallenge1_log', FILENAME = N'/var/opt/mssql/data/CorruptionChallenge1_log.ldf')
GO

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:dockersql2019data. For you, it’s wherever you mapped your persistent storage. In my case, C:dockersql2019data 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',  
NOUNLOAD,  REPLACE
GO

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.

The post Restore SQL Server Backup to Docker Container appeared first on sqlkitty.