Persisting data using named volumes

So far in this guide we've gone through spinning up a container and connecting to SQL Server. However containers are ephemeral by default so any changes made in SQL will be lost when we delete the container. Meaning that any databases we create will be lost, really lost. There's no way to get them back.

So let's run through how we can persist our databases from one container to another by using docker named volumes.

To create a named volume run: -

docker volume create mssqlsystem
docker volume create mssqluser

Notice we're creating two volumes here, one will be for the system databases and one will be for our user databases.

To check the volumes: -

docker volume ls

Now we can spin up a container with those volumes mapped: -

docker container run -d `
-p 15789:1433 `
--volume mssqlsystem:/var/opt/mssql `
--volume mssqluser:/var/opt/sqlserver `
--env ACCEPT_EULA=Y `
--env MSSQL_SA_PASSWORD=Testing1122 `
--name sqlcontainer1 `
mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04

The mssqlsystem volume is mapped to /var/opt/mssql which is the location of the system databases. The mssqluser volume is mapped to /var/opt/sqlserver which will be created for us.

By persisting the system databases we won't have to manually attach any user databases we create in any further containers that use these volumes. The master database will be persisted which will have a record of the database(s).

Let's run through that here.

First thing we need to do is grant the mssql user access to the location mapped to the mssqluser volume. We need to do this as SQL Server 2019 by default does not run as root within the container, it runs under the mssql user: -

docker exec -u 0 sqlcontainer1 bash -c "chown -R mssql /var/opt/sqlserver"

Now we can create a database: -

mssql-cli -S localhost,15789 -U sa -P Testing1122 `
-Q "CREATE DATABASE [testdatabase] ON PRIMARY (NAME='testdatabase',FILENAME='/var/opt/sqlserver/testdatabase.mdf') LOG ON (NAME='testdatabase_log',FILENAME='/var/opt/sqlserver/testdatabase_log.ldf');"

Check that the database has been created: -

mssql-cli -S localhost,15789 -U sa -P Testing1122 -Q "SELECT [name] FROM sys.databases;"

And now let's blow away that container: -

docker container rm sqlcontainer1 -f

Confirm that it's gone: -

docker container ls -a

OK, container is gone but we still have our volumes: -

docker volume ls

So let's now spin up another container, remapping the volumes: -

docker container run -d `
-p 15799:1433 `
--volume mssqlsystem:/var/opt/mssql `
--volume mssqluser:/var/opt/sqlserver `
--env ACCEPT_EULA=Y `
--env MSSQL_SA_PASSWORD=Testing1122 `
--name sqlcontainer2 `
mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04

And let's check that our database is there: -

mssql-cli -S localhost,15799 -U sa -P Testing1122 -Q "SELECT [name] FROM sys.databases;"

Excellent! By using named volumes we've persisted a database from one container to another!