Docker Express: Running a local SQL Server Express

In this <very brief> article, I’ll show you how to run a local, disposable SQL Server Express instance.

Prerequisites

Docker (for Windows)

The easiest way to ‘install’ a SQL Express, is to run a Docker container from the mssql-server-linux image. You can start one by running the following command from cmd or powershell.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=MyPass@word" -e "MSSQL_PID=Express" -p 1433:1433 -d --name=sql mcr.microsoft.com/mssql/server:latest

This will start a background Docker container named ‘sql’. The SA password is MyPass@word and it should be available through the connection string:

Server=localhost,1433;Initial Catalog=MyDb;Integrated Security=True;User Id=sa;Password=MyPass@word;

The Docker container exposes the container port 1433 to the localhost on 1433, this can conflict with any possible SQL instance you have running on your machine. To fix this, either stop the running SQL instance via services.msc or run the container on another host port.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=MyPass@word" -e "MSSQL_PID=Express" -p 1439:1433 -d --name=sql mcr.microsoft.com/mssql/server:latest

The command above will run a SQL Express instance on port 1439, your connection string needs to be altered as well:

Server=localhost,1439;Initial Catalog=MyDb;Integrated Security=True;User Id=sa;Password=MyPass@word;

Connecting via SSMS should be fairly straightforward:

Starting and Stopping the sql container

Normally, when your machine boots up, Docker for Windows starts up any containers it had running when shutting down. If, for some reason, your sql container is down, or malfunctioning, you can start and stop it manually.

docker start sql
docker stop sql

Backing up data

The running sql container can be seen as a volatile entity, if you remove the container, the data is gone with it.

docker rm -f sql

To keep your data, you’ll need to mount a Docker volume.

A Docker volume maps a folder from inside a container to a path on the host. In the sample below, we’ll map the C:\mount\sql folder on our Windows host to the data folder from the mssql-server-linux container, on path /var/opt/mssql/data

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=MyPass@word" -e "MSSQL_PID=Express" -p 1439:1433 -d --name=sql -v //c/mount/sql:/var/opt/mssql/data mcr.microsoft.com/mssql/server:latest

Now, you can backup your database using SSMS and copy the .BAK file from the C:\mount\sql folder.