Restore Your Windows Backups to the New SQL Server for Linux

At some point in November 2016, Microsoft finally made its highly trumpeted preview edition of SQL Server for Linux available to the general public.

Most developers know that Microsoft has a checkered past, full of closed-source applications. Recent years have seen them become friendlier towards open-source, but there is still work to be done.

The availability of a Docker container for the server enginer convinced me to give it a try on my laptop running Linux Mint, fully confident that the instance will run in isolation.

The challenge

While the server engine does run inside Docker, as of December 2016, the recommended client tools to connect to SQL Server for Linux are either residing on Windows (SQL Server Management Studio) or they are command-line tools containing hybrid Windows legacy codebase (sqlcmd).

I had a few database backup files from past projects, and I wanted to restore them on the new instance, without having to install the sqlcmd command line tool on my Linux box, or having to resort to a Windows machine or partition to perform the restore operation.

The approach

Install SQL Server inside Docker

There are various ways to install it, and several distributions are supported, but, at this point, the safest approach in my opinion is to install it in isolation, using the provided Docker container. Assuming you have already installed Docker, the initial steps are spelled out at this page provided by Microsoft:
Run the SQL Server Docker image on Linux, Mac, or Windows

Two minor nuisances you have to be aware of:

• One of the reasons why the docker container does not start is your master (sa) password not complying with the minimum password requirements (if you use 'password' as your actual password for example):

  docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=password' -p 1433:1433 -d microsoft/mssql-server-linux

• Once you set the sa password in a docker instance that persists the data files to disk, the first value will be saved to disk; subsequent values will not work:

  sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 \
  -v <host directory>:/var/opt/mssql -d microsoft/mssql-server-linux

Let's assume our data files will be stored here: /home/gizmo/projects/sqlserver/data (change the path based on your own folder structure). The folder structure must be in place before running docker, so that SQL Server creates its scaffolding in there.

For convenience, I created a shell script to start Docker (replace <YourStrong!Passw0rd> with your actual strong password):

#!/usr/bin/env bash

SQL_DATA_STORAGE_PATH="/home/gizmo/projects/sqlserver/data"

echo "------------------------------------"
echo "Starting SQL Server inside Docker"
echo "------------------------------------"
echo ""
echo "Data storage location: /var/opt/mssql maps to the real path: ${SQL_DATA_STORAGE_PATH}"
echo "Also make sure the password is strong enough or SQL Server will not start"
echo ""

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' -p 127.0.0.1:1433:1433 \
-v ${SQL_DATA_STORAGE_PATH}:/var/opt/mssql -d microsoft/mssql-server-linux

Once the SQL Server instance is up and running, we can see that it created the necessary files and directories inside the (previously empty) "data" parent directory:

total 28
drwxr-xr-x 7 gizmo  gizmo  4096 Nov 26 14:25 ./
drwxr-xr-x 4 gizmo  gizmo  4096 Nov 26 22:02 ../
drwxr-xr-x 2 root   root   4096 Nov 26 22:09 data/
drwxr-xr-x 2 root   root   4096 Nov 26 13:19 etc/
drwxr-xr-x 2 root   root   4096 Dec  4 17:38 log/
drwxr-xr-x 2 root   root   4096 Nov 26 13:19 secrets/
drwxr-xr-x 5 root   root   4096 Nov 26 13:19 .system/

Inside the Docker container, all that will be stored at /var/opt/mssql

Test Connectivity with Linux DB Clients

I am assuming you already have a client tool where you can connect to, and run queries against the newly dockerized server. There are several DB Clients available, either open source, or having a free community edition available:

• SQL Squirrel (fully open source)
• DB Visualizer
• DBeaver

Assuming all went well you will see the typical master database and be ready to open a query editor to issue SQL commands.