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
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 sqldocker 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.