Restoring a SQL Server Database in Docker

Last month I blogged about using Docker to run SQL Server as a quick and easy way to get SQL Server up and running.  While it continues to be immensely useful, there aren’t any user databases  running on it.  The only databases present are the system databases. Yes, I could manually create a database, but it would be a lot easier to have a sample database available.

How do we do restore a sample database, in a container, that is running on a Mac laptop?  Let’s check it out!

Disclaimer: Not a container expert so there might be various ways of doing it.  This is just how I’ve figured out how to make the magic happen.

Also, if you have not read the first blog on how to get SQL Server running in Docker on your Mac, take a few minutes to read through it.

Here are the steps that we will take to make this work:

1. Download one of the sample databases from I have a “mssql” directory in my local profile to make things easier
2. Make sure the container is started.  You can issue a “docker ps” command terminal to see which containers are running
3. Create a directory within the container
4. Copy the sample database backup file into the directory you just created
5. Restore the database onto the SQL instance that is running within the container

Sounds simple, right? Let’s see!

Create a directory

To create a directory within the container (remember that the container is running Linux), we can issue a command to the container that will create the directory.  Using Terminal (you can go to Spotlight to find the Terminal program or it is under Utilities in the Applications folder), execute the following command,

Docker exec -it sql2019 mkdir /var/opt/mssql/backups

Let us break that command down:

1. Docker – this indicates that we are going to be doing something with Docker
2. Exec – this tells the specified container to exec the command we pass into it
3. -it – this basically allows for an interactive session with the container
4. Sql2019 – this is the name of the container. You can specify the container name when you start the container or Docker will name it for you
5. Mkdir – this is short for “make directory”
6. /var/opt/mssql/backups – this is the directory path that is to be created.

Copy the Backup File

Now that the directory has been created, we need to get the backup file of the sample database into the container.  In my case, I am using AdventureWorks2017.bak

Docker cp ./mssql/AdventureWorks2017.bak sql2019:/var/opt/mssql/backups

Here is how that command breaks down:

1. Docker – this indicates that we are going to be doing something with Docker
2. cp – this is short for “copy”
3. ./mssql/AdventureWorks2017.bak – this is the path of the source file that is being copied into the container. The “.” Indicates start with whatever working directory I am in, which is my profile directory as indicated by the “jmorehouse$”
4. Sql2019 – this is the name of the container.
5. :/var/opt/mssql/backups – this is the destination directory that is within the container.

Once the command is complete, we can check to make sure that the file was copied successfully.

Docker exec -it sql2019 ls /var/opt/mssql/backups

The “ls” refers to “list”.  This is equivalent to executing a “dir” command in DOS.

Restore the Database

The backup file now resides within the container and we just need to tell SQL Server to restore it.  In this section, I will be using Azure Data Studio and native T-SQL commands.

Let us first check that SQL Server can see the file.

RESTORE FILELISTONLY FROM DISK=N’/var/opt/mssql/backups/AdventureWorks2017.bak’
GO

Excellent!  SQL Server can see the backup file which means that we can restore it.  Notice on the left-hand side, there are no user databases, just system databases. Also notice that the physical names of the database shown above are from the Windows Operating System.  Since SQL Server is running on Linux within the container, we will have to move the physical files to a different location.

RESTORE DATABASE AdventureWorks2017 FROM DISK=N’/var/opt/mssql/backups/AdventureWorks2017.bak’ WITH
MOVE ‘AdventureWorks2017’ to ‘/var/opt/mssql/data/AdventureWorks2017.mdf’,
MOVE ‘AdventureWorks2017_log’ to ‘/var/opt/mssql/data/AdventureWorks2017_log.ldf’
GO

Above we can see that the database was restored and then subsequently upgraded to the SQL Server 2019 database version.  If you refresh the Databases branch on the left-hand side, the AdventureWorks2017 database is now present!

Summary

Docker continues to be my current choice of “go to” when I need a quick and easy SQL Server solution to play around.  While I absolutely recommend Azure and its offerings, utilizing Docker on my local laptop is just faster, and frankly, fun to play around it.   Now that I can easily restore databases, it just makes it that much better of a solution for non-production uses.

Like these posts?  Make sure to subscribe to get alerted when new posts arrive!

© 2020, John Morehouse. All rights reserved.