I’ve previously talked about using named volumes to persist SQL Server databases but in that post I only used one named volume and I had to manually reattach the databases after the container spun up.
This isn’t really ideal, what we’d want is for the databases to automatically be attached to the new container. Thankfully there’s an easy way to do it, so let’s run through how here.
N.B. – Thanks to Anthony Nocentino (b|t) for pointing this out to me…it was a real d’oh moment 🙂
First thing, is to create two named volumes: –
docker volume create mssqlsystem docker volume create mssqluser
And now spin up a container with the volumes mapped: –
docker container run -d -p 16110:1433 \ --volume mssqlsystem:/var/opt/mssql \ --volume mssqluser:/var/opt/sqlserver \ --env ACCEPT_EULA=Y \ --env SA_PASSWORD=Testing1122 \ --name testcontainer \ mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu
The mssqluser named volume is going to be mounted as /var/opt/sqlserver and the mssqlsystem volume is going to be mounted as /var/opt/mssql. This is the key to the databases automatically being attached in the new container, /var/opt/mssql is the location of the system databases.
If we didn’t mount a named volume for the system databases any changes to those databases (particularly for the master database) would not be persisted so the new container would have no record of any user databases created.
By persisting the location of the system databases, when SQL starts up in the new container the changes made to the master database are retained and therefore has a record of the user databases. This means the user databases will be in the new instance in the new container (as long as we’ve persisted the location of those databases, which we’re doing with the mssqluser named volume).
Let’s create a database on the mssqluser named volume: –
USE [master];
GO
CREATE DATABASE [testdatabase]
ON PRIMARY
(NAME = N'testdatabase', FILENAME = N'/var/opt/sqlserver/testdatabase.mdf')
LOG ON
(NAME = N'testdatabase_log', FILENAME = N'/var/opt/sqlserver/testdatabase_log.ldf');
GO
And now blow the container away: –
docker kill testcontainer docker rm testcontainer
That container is gone, but we still have our named volumes: –
docker volume ls
So we can now spin up another container, using those volumes: –
docker container run -d -p 16120:1433 \ --volume mssqlsystem:/var/opt/mssql \ --volume mssqluser:/var/opt/sqlserver \ --name testcontainer2 \ mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu
UPDATE – As pointed out in the comments by Patrick Flynn, the environment variables ACCEPT_EULA and SA_PASSWORD do not need to be set for the second container as we have persisted the system databases so their values have been retained
Connect to the SQL instance in the new container…
And boom! The database is there!
Thanks for reading!
DBA From The Cold 
Please note that once you persist the System files the sa password is actually that from when the image was first persisted. In your example the Password used for testcontainer2 is not relevant as the password from testcontainer is retained.
That’s a very good point. I’ve updated the post to reflect that. Thank you.
Great article thanks a lot .. I am trying to achieve this with the windows version of the SQL Server image do you know if its possible to achieve the same and persist the data using the windows image ?
Yes you can. Just change the volume mapping to something like: –
–volume mssqlsystem:C:\sqlserver\
Hi thanks a lot for your reply, this is ok if you already have and copy database to that volume or create databases (like you showed in a previous post) explicitly on that volume when the container runs, but what I was looking for is to have the same as the linux version where when you add the database to the default installation of sql server it saves it on the volume and when the container is destroyed it automatically reloads the databases from the volume like on Linux. I tried to replace the volumes with the default directory where the system dbs and datas are stored eg : xxx:\program files\Microsoft SQL Server\xxx\MSSQL\DATA but it didnt work so I am not sure if there is a way to achieve the same as on linux where it restores everything automatically ..
You’re going to have issues there as there’s spaces in the directory path.
What image are you using?
I wanted to use the microsoft/mssql-server-windows-express:2017 .. I can live with the Linux version which is much smaller in size anyway but still was wondering if its possible somehow to keep the state on volumes with auto mounting of the databases on windows as well 🙂
I’m not sure how to deal with the spaces in the directory to the location of the master database files to be honest.
One way of getting around that would be to build a custom image with the master database files in a different location that can be persisted.
Has anyone figured out a way to persist the system databases on a Windows SQL server image? Like Maxime above I can persist user databases but I can’t map the default system database path to a docker volume. My custom Windows SQL image uses C:\sqldata as the default: INSTALLSQLDATADIR=”C:\sqldata”
I run the following command and the master mdf file is placed here: C:\sqldata\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf
docker run -d -p 1435:1433 -e “ACCEPT_EULA=Y” -e “SA_PASSWORD=xxxxx” –name sql01 sql2017:latest
However, running the following command causes SQL to fail to start because it can’t map C:\sqldata\ to a docker volume named sqldata02
docker run -d -p 1435:1433 -e “ACCEPT_EULA=Y” -e “SA_PASSWORD=”xxxxx” –name sql01 -v “sqldata02:C:\sqldata” sql2017:latest
Docker logs show this message:
start-service : Failed to start service ‘SQL Server (MSSQLSERVER)
There must be some other way to do this with a Windows SQL image.
Ping me an email and we can have a look at this – dbafromthecold@gmail.com
Do you know what is the default path if we don’t mount a specific volume ?
The default path for databases in SQL on linux is /var/opt/mssql/data