Persist SQL Server Container Data-Part2

In Part1, I demonstrated the most basic way to persist data in SQL Server container however in this post which is part2, I will introduce kind of advance ways to persist data when using SQL Server container. Please click here if you have not setup docker yet within your environment or new to docker container technology.

As best practices, we should have separate drives for SQL Server data file, log file, Tempdb data files, and even backup files. For the purpose of this demo, I will deploy a new SQL Server container that will have separate local drives for data, log, and backup files.
Same procedures can be apply withing Linux OS environments. You just have to change the drive format.

Below there is an explanation of used parameters:

• -e (or -env) – sets environment variables, in our case we accept End User License Agreement and sets a password for “sa”
• –hostname – sets container hostname, we can omit this, then docker will generate its own name
• -p (or –publish) – publishes a container’s port to the host. In our case default SQL port (1433) is published on port 1421
• -v (or –volume) – binds mount a volume. It is the location of persistent storage for SQL Server databases
• –name – assigns a name to the container
• -d (or–detach ) – runs the container in background and print container ID

Run below script to deploy a new SQL Server container.

$SQLServerName = "Demo"
$pw = "Test!123"
docker run -e "ACCEPT_EULA=Y"`
    -e "MSSQL_SA_PASSWORD=$pw"`
    --name "$SQLServerName"`
    --hostname "$SQLServerName" `
    --env MSSQL_DATA_DIR="/var/opt/mssql/data" `
    --env MSSQL_LOG_DIR="/var/opt/mssql/dblog" `
    --env MSSQL_BACKUP_DIR="/var/opt/mssql/backup" `
    --env MSSQL_AGENT_ENABLED="true" `
    -p "1421:1433"`
    -v "C:\mssql\data:/var/opt/mssql/data"`
    -v "C:\mssql\log:/var/opt/mssql/dblog"`
    -v "C:\mssql\errorlog:/var/opt/mssql/log"`
    -v "C:\mssql\backup:/var/opt/mssql/backup"`
    -v "C:\mssql\secrets:/var/opt/mssql/secrets"`
    -d "mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04"

Wait until SQL Server Container deployment is completed and able to access it. Once completed, use below scripts to connect to SQL Server.

docker exec -it Demo /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "Test!123"

Let’s create Demo_DB database.

CREATE DATABASE Demo_DB;

Go to the local drives that you mounted to SQL Server docker container then verify if data and log files are present as seeing from below screenshot.

Now, let’ us backup Demo_DB database

BACKUP DATABASE Demo_DB TO DISK='/var/opt/mssql/backup/demo_db.bak';

Don’t forget to verify if Demo_DB backup file is present to the local drive that mounted to SQL Server docker container.

Conclusion:
Again this is kind of advance but not the recommended way. I would highly suggest you to wrap up all the logic within a dockerfile or docker-compose file which is the prepared way.