
About a month ago, I learned something new. I learned how to run SQL Server 2019 in Docker and how to set up my demo environment in a container. Cool stuff! I like whales. Whales are cool.
While learning, I started writing this blog post. Then I got distracted and never finished it. This weekend, I had to set up my demo environment again. It was the perfect opportunity to update the content and finally publish this post.
(Why did I have to set up everything again? Oh, it’s a long story that involves disk cleanup and a Cathrine who likes to delete things to keep her computer tidy. Ok, it’s not really a long story. It was more like “oops, I accidentally deleted my container”.)
Anyway! Back to the actual content.
In this post, I share my approach and code snippets for:
- Installing Docker
- Getting SQL Server 2019
- Running SQL Server 2019 in a Docker Container
- Restoring Demo Databases (AdventureWorks and WideWorldImporters)
Installing Docker
I knew nothing about Docker or containers a month ago. But! I’m lucky to have smart friends :) Andrew Pruski (@dbafromthecold) wrote Running SQL Server 2019 CTP in a Docker container as part of his brilliant blog post series on containers.
I decided to start with his walkthrough and do exactly what he did. It worked pretty well for me! See below :)
Getting SQL Server 2019
Once you have Docker installed, pull the SQL Server 2019 image by executing the following code in PowerShell:
docker pull mcr.microsoft.com/mssql/server:2019-latest
Pulling an image basically means downloading all the files needed to run a container. You can think of this as downloading the SQL Server installation files.
I originally used the SQL Server 2019 CTP2.1 image, but the code in this post has been updated to a newer version. If you want to run a different version of SQL Server, you can find a list of all the available images on the Microsoft SQL Server page on Docker Hub. You can also get the list of images programmatically.
Running SQL Server 2019 in a Docker Container
When I started writing this post a month ago, I followed Microsoft’s blog post and copied files into my container. Since then, I have learned a few new things. I now run my containers with a bind mount (shared drive). This means that I have access to a local folder on my computer from inside my docker container.
First, make sure to enable Shared Drives in Docker:

Then, run the container by executing the following code in PowerShell:
docker run `
--name SQL19 `
-p 1433:1433 `
-e "ACCEPT_EULA=Y" `
-e "SA_PASSWORD=<SuperStrongPassword>" `
-v C:\Docker\SQL:/sql `
-d mcr.microsoft.com/mssql/server:2019-latest
I name my container SQL19 and specify that I want to map my local folder C:\Docker\SQL to the container path /sql. For more information about the other options, read the Get Started article by Microsoft.
Running a container is similar to installing a local SQL Server instance. Just much faster and easier :)
Finally, verify that the container is running by executing the following code in PowerShell:
docker ps -a
The ps stands for Process Status, and -a will show all processes regardless of status. If the container is not running, you have to (re)start it:
docker start SQL19
You can now connect from SQL Server Management Studio (SSMS) to your container as SA using your <SuperStrongPassword>:

If you specified a different port when running the container, you will have to connect using the port number, for example localhost,1401
Restoring Demo Databases
I had already downloaded the AdventureWorks and WideWorldImporters .bak files to my C:\Docker\SQL\Backup folder.
If you open Restore Database from SQL Server Management Studio (SSMS) and navigate to Locate Backup File, you will see the /var/opt/mssql/data path:

Now… Here’s where I got confused. The current path is using forward slashes. When I ran the container, I also mapped my local folder C:\Docker\SQL to the container path /sql. To me, it was logical to try /sql:

Nope! No can do. You have to use \sql:

I have no idea why, and I haven’t figured out if this is a bug or by design. (Do you know? Please comment!) I can only guess that since SSMS is a Windows application, it is using Windows paths (with backslashes), even though it is showing the Linux paths (with forward slashes).
Anyway! Select your .bak file…

…aaaaand SUCCESS! :D

Yay! The WideWorldImporters demo database is now restored to SQL Server 2019 running in a Docker container:

How cool is that? Just repeat the process for any remaining databases. Mission accomplished :)
Restore Demo Databases Script
If you do this more than a few times a year, definitely automate the restore process using T-SQL or PowerShell! I use the following, standard script:
USE [master];
GO
RESTORE DATABASE [AdventureWorks]
FROM DISK = N'/sql/Backups/AdventureWorks2017.bak'
WITH FILE = 1,
MOVE N'AdventureWorks2017' TO N'/var/opt/mssql/data/AdventureWorks.mdf',
MOVE N'AdventureWorks2017_log' TO N'/var/opt/mssql/data/AdventureWorks_log.ldf',
NOUNLOAD, STATS = 5
GO
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'/sql/Backups/AdventureWorksDW2017.bak'
WITH FILE = 1,
MOVE N'AdventureWorksDW2017' TO N'/var/opt/mssql/data/AdventureWorksDW.mdf',
MOVE N'AdventureWorksDW2017_log' TO N'/var/opt/mssql/data/AdventureWorksDW_log.ldf',
NOUNLOAD, STATS = 5
GO
RESTORE DATABASE [AdventureWorksLT]
FROM DISK = N'/sql/Backups/AdventureWorksLT2017.bak'
WITH FILE = 1,
MOVE N'AdventureWorksLT2012_Data' TO N'/var/opt/mssql/data/AdventureWorksLT.mdf',
MOVE N'AdventureWorksLT2012_Log' TO N'/var/opt/mssql/data/AdventureWorksLT_log.ldf',
NOUNLOAD, STATS = 5
GO
RESTORE DATABASE [WideWorldImportersDW]
FROM DISK = N'/sql/Backups/WideWorldImportersDW-Full.bak'
WITH FILE = 1,
MOVE N'WWI_Primary' TO N'/var/opt/mssql/data/WideWorldImportersDW.mdf',
MOVE N'WWI_UserData' TO N'/var/opt/mssql/data/WideWorldImportersDW_UserData.ndf',
MOVE N'WWI_Log' TO N'/var/opt/mssql/data/WideWorldImportersDW.ldf',
MOVE N'WWIDW_InMemory_Data_1' TO N'/var/opt/mssql/data/WideWorldImportersDW_InMemory_Data_1',
NOUNLOAD, STATS = 5
GO
RESTORE DATABASE [WideWorldImporters]
FROM DISK = N'/sql/Backups/WideWorldImporters-Full.bak'
WITH FILE = 1,
MOVE N'WWI_Primary' TO N'/var/opt/mssql/data/WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'/var/opt/mssql/data/WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'/var/opt/mssql/data/WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1',
NOUNLOAD, STATS = 5
GO
Summary of SQL Server 2019 in Docker
In short, it probably takes longer to read this post than to actually create a demo environment in a Docker container. If you already have Docker installed and a copy of your .bak files, it’s as easy as:
- Pull
- Run
- Restore!
Resources
- Running SQL Server 2019 CTP in a Docker container (by Andrew Pruski)
- Quickstart: Run SQL Server container images with Docker (Microsoft Docs)
- Restore a SQL Server database in a Linux Docker container (Microsoft Docs)
- Use SQL Server Management Studio on Windows to manage SQL Server on Linux (Microsoft Docs)
- Microsoft SQL Server page on Docker Hub
Hi Catherine! The “slash” issue is interesting. If you have your backups just in the top “/sql” directory, as opposed to a directory below (such as /sql/backups, like you), then SSMS will throw an error with not being able to find the backup, and the backup media path will be mixed. In my example, it was “\sql/wwi.bak”. Using “restore headeronly from disk=’/sql\wwi.bak'” in SSMS works, however.
Interestingly enough, the “Backup File Location” stays at “\sql”, until you browse to another lower directory, then it flips to “/sql/”
Tested this with SSMS SQL Server Management Studio 15.0.18068.0. My guess is bug, but not sure.
If you try entering “/sql” then hit the refresh button/icon/arrow/whateveryouwanttocallit it’ll work. Typing “/sql” then pressing enter throws the error – as to why, I’ve never got back to fully investigating so have no idea other than a potential bug ;)
Cathrine, thanks very much for this post. Other than the fact I need to remember that reading is fundamental, it worked like a charm. One problem I ran into was my password didn’t meet the strength requirements, so my container wouldn’t start, though docker start didn’t throw an error. I had to run docker logs to figure that one out.
Nice post. Would also add that docker can give lot of weird behavior if you have installed it on a Corp laptop with Trend AV.
Had to do some exceptions in the AV for certain folders to get it running.
You may find this documentation very useful in the future, as did I. Persistence of data over the life of multiple containers.
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-docker?view=sql-server-2017#persist
SSMS didn’t really want to play so i ended up restoring the databases from script. Otherwise excellent.
I am trying to find out how to enable full context, so I can create adventureworks, running on a docker image on mac. configuration assistant is not available any ideas? Thanks
Hi Cathrine. Thanks for this post. I’ve tried to do this a few times, but just got errors I didn’t understand. Following your example got me there in minutes!
Awesome!!! Thank you so much.