Persisting data using data volume containers

In a previous page we went through how to persist databases from one container to another using docker named volumes. In this post I want to go through another option, using data volume containers

In this method we create a container with volumes and then mount the volumes from that container to another container, let's get started.

The first thing to do to get started is to create (not run) a container from an image with a couple of volumes specified.

We don't need SQL running in this container so I'm going to use the ubuntu:18.04 image: -

docker container create --name datastore `
--volume /var/opt/sqlserver/data `
--volume /var/opt/sqlserver/log `
--volume /var/opt/sqlserver/backups `
ubuntu:18.04

So here we've created a container with three volumes: -

/var/opt/sqlserver/data
/var/opt/sqlserver/log
/var/opt/sqlserver/backups

Confirm that the container has been created: -

docker container ls -a

In the background what's happened here is that docker has created us named volumes: -

docker volume ls

Now we can spin up a sql container with volume mapped from the data container using the --volumes-from flag: -

docker container run -d `
--publish 15789:1433 `
--volumes-from datastore `
--env ACCEPT_EULA=Y `
--env SA_PASSWORD=Testing1122 `
--env MSSQL_DATA_DIR=/var/opt/sqlserver/data `
--env MSSQL_LOG_DIR=/var/opt/sqlserver/log `
--env MSSQL_BACKUP_DIR=/var/opt/sqlserver/backup `
--name testcontainer1 `
mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04

Confirm the sql container is running: -

docker container ls -a

Now as SQL within the container runs as the mssql user, we'll need to change the owner of the volumes: -

docker exec -u 0 testcontainer1 chown -R mssql /var/opt/sqlserver

Cool, we're all set to create a database: -

mssql-cli -S localhost,15789 -U sa -P Testing1122 -Q "CREATE DATABASE [testdatabase1];"

Confirm the location of the database files: -

mssql-cli -S localhost,15789 -U sa -P Testing1122 -Q "USE [testdatabase1]; EXEC sp_helpfile;"

Let's now blow that sql container away: -

docker container rm testcontainer1 -f

Check our containers: -

docker container ls -a

We still have our data volume container, and we also have the named volumes: -

docker volume ls

So now let's create another container, mapping the volumes from the data volume container again: -

docker container run -d `
--publish 15799:1433 `
--volumes-from datastore `
--env ACCEPT_EULA=Y `
--env SA_PASSWORD=Testing1122 `
--env MSSQL_DATA_DIR=/var/opt/sqlserver/data `
--env MSSQL_LOG_DIR=/var/opt/sqlserver/log `
--env MSSQL_BACKUP_DIR=/var/opt/sqlserver/backup `
--name testcontainer2 `
mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04

Confirm that the container is up and running: -

docker container ls -a

Check that the database files are there: -

docker exec testcontainer2 ls -al /var/opt/sqlserver

Now, as we didn't persist the master database location, we'll need to manually attach the database: -

mssql-cli -S localhost,15799 -U sa -P Testing1122 `
-Q "CREATE DATABASE [testdatabase1] ON PRIMARY (NAME='testdatabase1',FILENAME='/var/opt/sqlserver/data/testdatabase1.mdf') LOG ON (NAME='testdatabase1_log',FILENAME='/var/opt/sqlserver/log/testdatabase1_log.ldf') FOR ATTACH;"

And now confirm the database is there: -

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

Cool! We've persisted a database from one container to another using a data volume container!

To clean up: -

docker container rm $(docker container ls -aq) -f && docker volume prune -f