Persisting data using bind mounts

On previous pages we've gone through how to persist databases from one container to another by using: -

Named Volumes
Data Volume Containers

Another way to is mount a volume from the Docker host into a container.

Let's create a couple of directories on the host: -

mkdir C:\sqlserver\data
mkdir C:\sqlserver\log

Now, run a container: -

docker container run -d  `
--publish 15789:1433 `
--volume C:\sqlserver\data:/opt/sqlserver/data `
--volume C:\sqlserver\log:/opt/sqlserver/log `
--env ACCEPT_EULA=Y `
--env MSSQL_SA_PASSWORD=Testing1122 `
--env MSSQL_DATA_DIR=/opt/sqlserver/data `
--env MSSQL_LOG_DIR=/opt/sqlserver/log `
--name sqlcontainer1 `
mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04

This will map the volumes on the host to /opt/sqlserver/data and /opt/sqlserver/log in the container.

We're also using environment variables to set the SQL Server default data and log locations to those volumes in the container.

Let's confirm that the container is up: -

docker container ls -a

And now let's create a database: -

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

No need to set the data and log file location as we've set the defaults to the volumes in the container that are mapped to the volumes on the host.

We can check the database files by running: -

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

And we can confirm the files on the host by running: -

ls C:\sqlserver\data
ls C:\sqlserver\log

And there are the database files on the host!

Ok, blow the container away: -

docker rm sqlcontainer1 -f

And spin up a new container, remapping the volumes from the host: -

docker container run -d  `
--publish 15799:1433 `
--volume C:\sqlserver\data:/opt/sqlserver/data `
--volume C:\sqlserver\log:/opt/sqlserver/log `
--env ACCEPT_EULA=Y `
--env MSSQL_SA_PASSWORD=Testing1122 `
--env MSSQL_DATA_DIR=/opt/sqlserver/data `
--env MSSQL_LOG_DIR=/opt/sqlserver/log `
--name sqlcontainer2 `
mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04

Confirm the container is up and running: -

 docker container ls -a

Cool. Ok, now as we didn't persist the master database location we'll need to manually attach the database into SQL in the new container: -

 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 check that the database is there: -

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

And there's the database! We've persisted a database from one container to another by mounting volumes from the host.