Restoring SQL Server Database on Linux Using SQL Operations Studio

I’ve had a busy few months of travel. This has cut into my blogging time as well as time to play with new things. One of those things was getting started on restoring a SQL Server database on Linux using SQL Operations Studio.

With the short Thanksgiving week, I set aside a few hours to configure and document how to get SQL Server running in a Docker container on my iMac, connect using SQL Operations Studio, and restoring a SQL Server database.

In order to see this magical unicorn for yourself, you need two things: Docker and SQL Operations Studio. You can download Docker for Mac here, and you can download SQL Operations Studio for Mac here. I will leave the installation of each as an exercise for the reader.

With those tools installed, let’s get started.

Running SQL Server inside Docker on iMac

After Docker is installed you need to configure it to use at least 4GB of memory, like this:

You also need at least 4GB of available disk space. And don’t forget any of the other requirements for running SQL Server on Linux.

The next step is to pull the latest version of SQL Server from the Docker Hub. We can do this with a simple bash command:

sudo docker pull microsoft/mssql-server-linux:latest

You can also use Powershell. The commands for both are on this page, in case you wanted to see them. I’m going to use the default Terminal app on my iMac:

When that is complete I will be able to start the instance with the following command:

sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' 
-p 1433:1433 -d microsoft/mssql-server-linux:latest

For a full list of Docker run commands, go here. And for more information on the the Docker repository image for SQL Server on Linux, go here.

Lastly, we can verify that the instance is running with the following command:

sudo docker ps -a

You want the status to show as “Up”. If it shows anything else then you will want to check out the troubleshooting guide here.  This command also shows that our Docker container has a name. We could have set the name upon startup, but we didn’t. So, we will rename it now, using this command, where we replace CONTAINERID with the actual container id:

sudo docker rename CONTAINERID 'PANCETTA'

OK, the instance is up and running, with a name of PANCETTA. Next, we will copy a database backup file into the container. FIrst, I will create our own directory for backup files using one line:

sudo docker exec -it PANCETTA mkdir /var/opt/mssql/backups

I will navigate to a directory where I have a backup file, and then I can do the copy again with one line of code:

sudo docker cp AdventureWorks2016CTP3.bak PANCETTA:/var/opt/mssql/backups

Next, let’s connect to the instance and restore that backup.

Restoring SQL Server Database on Linux in SQL Operations Studio

First, we need to connect to the instance. We will start SQL Operations Studio and connect like this:

Once connected, we click on the ‘Restore’ icon on the dashboard to arrive at the Restore Database screen. There we will navigate to the directory where we have placed our backup file:

Click OK, and the main screen will look like this:

We can start the restore right away or generate a script. The script should look familiar:

USE [master]
RESTORE DATABASE [AdventureWorks2016CTP3]
FROMDISK= N'/var/opt/mssql/backups/AdventureWorks2016CTP3.bak'
WITH FILE = 1, MOVE N'AdventureWorks2016CTP3_Data' TO N'/var/opt/mssql/data/AdventureWorks2016CTP3_Data.mdf',
MOVE N'AdventureWorks2016CTP3_Log' TO N'/var/opt/mssql/data/AdventureWorks2016CTP3_Log.ldf',
MOVE N'AdventureWorks2016CTP3_mod' TO N'/var/opt/mssql/data/AdventureWorks2016CTP3_mod',
NOUNLOAD, STATS = 5

When the restore is complete, we can then navigate to the AdventureWorks2016 database:

You are looking at SQL Server, running on Linux, in a Docker container, on my iMac, connected through SQL Operations Studio. Yes, it’s as awesome as it looks.

And we could script all of this out, making it a repeatable process. I could configure a handful of containers with different configurations, spin them up, test some code, and spin them down with a push of a button.

Summary

I love living in the future. I love it so much, I may never leave.