Restoring an MSSQL DB Running Docker Using Linux… on a Mac

First off, I wish I could throw more technology at that title.

Let me start with the set up. I will say I am no DBA, I am simply an architect/developer that often has to solve problems quickly and move on.

I was recently tasked with extracting data from an MS SQL Database; I was given a database backup and a short timeline. The first thing I needed to do was set up MS SQL Server and recover the database from the backup file to begin working.

I wanted to do this quick, and easy, and I needed to do this without provisioning a server, or installing SQL Sever, etc. After all I have a Mac and It is not like I can easily install SQL Server.

My first course of action was to leverage Docker; luckily Microsoft supports an official container that includes SQL Server on Linux for Docker Engine; which includes everything we need to restore a database from a backup.

See https://hub.docker.com/_/microsoft-mssql-server for full details.

What I wanted to do here, was walk through the few easy steps on how I restored a database from a backup into a linux docker container running MSSQL. The reality is, I was shocked at how easy it was to do.

Prerequisites:

First off, We need Docker installed. If you do not — please install docker. There are numerous getting started tutorials, but its always best to go to the source: https://docs.docker.com/get-docker/

We also need a database back up to work with. For the sake of our example we can use AdventureWorks2017.bak. This is Microsoft’s ‘legendary’ AdventureWorks database. Let’s download that to use as our recovery database.

Now we are ready to begin!

Steps for our Container:

As I mentioned before the first thing we need to do is grab the Microsoft SSQL for Linux container.

Open a terminal window and run:

docker pull mcr.microsoft.com/mssql/server

Lets try to run it:

docker run -e 'ACCEPT_EULA=Y' --name linux_mssql -e 'SA_PASSWORD=Strong!&P@$$w0rd' -e 'MSSQL_PID=Express' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest-ubuntu

This should have started running the docker container. We have a couple of things here:

ACCEPT_EULA — confirms that we accept the End-User Licensing Agreement. (PSA: one should always read Licensing Agreements before opting in)

name is what I called my running container — in my case I called it linux_mssql — I could have used any name or not included the name flag at all.

SA_PASSWORD — is the DB system administrator (SA) password used to connect to the SQL Server once the container is running. Keep in mind this does come with some password requirements/restrictions. Make sure you use a strong password.

MSSQL_PID — Is the MSSQL version that I want the container to run. I am using Express in my example, however, the default is Developer.

Lets run the following to verify that we are running as expected:

docker container ls

if everything is up as expected you should see the following:

Now we have our container running. We need to do a few things. First we need to get our backup into that container so that we can run the restore process.

Let us begin by creating a directory that will hold our backup. We can easily do this by running the below command:

sudo docker exec -it linux_mssql mkdir /var/opt/mssql/backup

Keep in mind ‘linux_mssql’ is simply what I called my running container; it maybe different for you.

Before we go too far; let us get in and see what we have so far.

We begin by getting a bash shell in the container.

docker exec -it linux_mssql /bin/bash

Once we do that, we should get something like

Let us now connect to SQL Server using the sqlcmd tool inside the container. The sqlcmd utility is a command-line utility that will allow us to execute Transact-SQL statements and scripts. We will use sqlcmd to run our restore command.

we can get into sqlcmd by running the following:

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa

you will be prompted for your password ( we could have added the -P option and included the password if we wanted to do it all on one line).

While we are in here let’s take a quick look at our Databases. Let’s Run

Select NAME from sys.databases
GO

This queries for all available databases in our running instance.

What we want to do now is restore AdventureWorks2017 database. Remember the backup file we downloaded earlier? we need to get that copied into our container.

In another terminal window we can simply run:

sudo docker cp AdventureWorks2017.bak linux_mssql:/var/opt/mssql/backup

*Note: I am in the directory were my backup copy exists — you may need to include the path of the file to copy from. Now the command we are going to run to restore our database is:

RESTORE DATABASE [AdventureWorks2017] FROM DISK = N’/var/opt/mssql/backup/AdventureWorks2017.bak’ WITH RECOVERY, MOVE "AdventureWorks2017" TO N '/var/opt/mssql/data/AdventureWorks2017.mdf', MOVE 'AdventureWorks2017_log' TO N'/var/opt/mssql/data/AdventureWorks2017_log.ldf'

Let us take a quick look at what we are running here, we want to restore the AdventureWorks2017 database from Disk, and we are pointing to where we copied our backup file. In my example I restore with RECOVERY command.

When the RECOVERY command is used, during the restore process all uncommitted transactions are rolled back, and once all uncommitted transactions are rolled back the database is placed into a ready state and it’s ready to use.

we could, alternatively, have used the NORECOVERY command. Which requests that no uncommitted transactions be rolled back; we typically see NORECOVERY restores used in situations where a further transaction log file has to be applied, or there may be an additional differential file to be applied.

The next bit of the command

...MOVE “AdventureWorks2017” to N’ /var/opt/mssql/data/AdventureWorks2017.mdf’...

is critical; as we are taking a backup from a Windows machine and attempting to RESTORE to our Linux container.

If we skip this step we will receive the following:

Looking at the errors we can see that our issue is that the container does not match the Windows operating systems expected SQL Server paths. so we simply want to provide a new location for our primary data file (.mdf) and our transaction log file (.ldf). We prevent this by using the MOVE command

MOVE "AdventureWorks2017" TO N '/var/opt/mssql/data/AdventureWorks2017.mdf', MOVE 'AdventureWorks2017_log' TO N'/var/opt/mssql/data/AdventureWorks2017_log.ldf'

*One obvious or maybe not so obvious note, if you are importing your own database you could have a secondary data file (.ndf) which you would need to also address in the same manner.

Now enter GO, and if all goes well; you should see the following:

We should now be ready to use the database.

If we query our sys.databases table, we should see the AdventureWorks2017 database:

Select NAME from sys.databases
GO

For good measure, let us run a command to make sure our database has data.

we can run a query to get the count from the product table.

use AdventureWorks2017
GO
select count(*) from production.product
GO

Conclusion

This was a short walk through of the steps to restore an MSSQL Database using Docker. If you found these steps useful or if you had issues following along drop me a line.