In this 17^th article of the series (see the full article index at bottom), we will discuss the concepts of database backup-and-restore operations on SQL Server Docker containers. This is certainly the era of containers era and it is buzz right now, so let us understand the importance of backup-and-restore the databases on the Docker containers.

Docker containers are systems that are small, light weight, isolated, and segmented subsystems built on the Linux or Windows host system. It’s just running an application on the host machine.

This article discusses the following topics:

• Pre-requisites
• Steps to initiate a database backup and copy the backup file across the containers
• Step by step to restore a database
• Pulling the latest SQL Server 2017 container images from the docker registry
• Running the downloaded Docker container image using docker run command
• Details the steps to create the database in the container
• Explain data persistence in Docker
• And more…

Pre-requisites

The list describes essential prerequisites to follow along the article

1. Docker engine 1.8+ on Linux/Mac/Windows
2. Min 2 GB disk space and Min 2 GB RAM
3. Open the Super User console
4. Basic understanding of Docker containers

Getting started

To build the SQL Server 2017 container use the docker pull command and specify the reference to the latest available Docker container image from the Microsoft repo registry.

docker pull microsoft/mssql-server-linux:2017-latest

To run the SQL Server Docker container, in the background, use docker run command.

docker run -e ‘ACCEPT_EULA=Y’ -e ‘MSSQL_SA_PASSWORD=SQLShack$2018’ –name shackdemo1 -p 1401:1433 -d microsoft/mssql-server-linux:latest

To instantiate a bash session, run the bash executable using docker exec command.

docker exec –it shackdemo1 bash

Now, a Bash session is opened for SQLShackDemo1 container. Connect to the SQL instance using sqlcmd and create a new database named SQLShackDemo.

Next, create a sample table SQLAuthor and insert few dummy records into the table.

Let us create a database backup using backup database command

BACKUP DATABASE [SQLShackDemo] TO DISK = N’/var/opt/mssql/backup/SQLShackDemo.bak’ WITH FORMAT, INIT, COMPRESSION,STATS = 10

Let’s exit the docker session. We need to make sure that the backup file is not trapped within the container.  It is very easy to move files in-and-out of the container using the docker cp command. 

Let’s take a look at the docker cp command. It takes two parameters.

1. First, the container name, shackdemo1, followed by colon and then path of the backup file to copy.
2. Second, the host path to copy the file.

Now, browse the host path to check the attributes of the copied backup file.

ls –l /tmp/SQLShackDemo.bak

In this section, create a new container named shackdemo2. As we can see that, the newly created SQL Server 2017 Docker container instance just have the system databases.

[root@localhost thanvitha]# docker run -e ‘ACCEPT_EULA=Y’ -e ‘MSSQL_SA_PASSWORD=SQLShack$2018’ –name shackdemo2 -p 1402:1433 -d microsoft/mssql-server-linux:latest 714269288a08da8775eb59dad6c5d4c2b2d6bad833043e81fcf4afc6586be7aa [root@localhost thanvitha]# docker exec -it shackdemo2 bash root@714269288a08:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA Password:

Now, exit the SQL Docker container instance, shackdemo2. Copy the backup file from the host machine to the shackdemo2 container.

Let’s take a look at copying backup files into the new container using the same docker cp command. We’ll use docker cp again, but we’ll reverse the parameters. 

1. First goes the file path of the host followed by a space and then the container name shackdemo2, a colon and the path where we want copy the file.
2. Second, the target path is going to be sqldemo2:var/opt/mssql/data/SQLShackDemo.bak

Docker cp /tmp/SQLShackDemo.bak sqldemo2:var/opt/mssql/data/

Next, run the database restore command to restore the database.

RESTORE DATABASE [SQLShackDemo] FROM DISK = N’/var/opt/mssql/data/SQLShackDemo.bak’ with REPLACE

We can see that SQLShackDemo database is listed. Let’s query the tables to view the sample data.

Data externalization

Let’s walk through the details of how to externalize the application database data file. As long as the containers remain intact with the host, the data will remain safe even if the container is stopped or restarted. However, if you remove the container your databases get removed with it and it’ll be gone forever. 

Let’s discuss the Docker’s solution that keeps the data safe across containers. Using Docker data volume (-v) option, it is that simple to share the data. During the SQL Server container creation process, map to the SQL Server database file directory using –v parameter. 

To create a new container named sqldemo use the following docker run command.

Now, check the status of the newly created docker container sqldemo

-v, this is where we’re going to specify the volume information. Create a new volume sqlservervolume and map it with the internal directory structure of SQL Server instance, or the Linux instance, where SQL Server stores its data and log files. In this case, the default volume location /var/opt/mssql is mentioned.

Now, we’ve created the Sqldemo container. Now, instead of storing database files within the container, the data files are stored /var/opt/mssql directory, the container data is now externalized. It’s stored in the name sqlservervolume data volume. We can take a look at volumes using docker volume ls. 

Let’s open a docker session and create a database using docker exec and sqlcmd commands.

So far, we’ve created a database sqlvolumetestDB. Let’s go ahead quit SQL shell and exit out of the Linux shell. Now, go ahead and remove the container. Before that though, stop the container using docker stop command. Once it’s stopped, remove the sqldemo container using docker rm command but the volume still exists.

Now, create a new SQL container and link the existing volume to the SQL container. The new SQL instance is going to be Newsqldemo.

Let’s go into the Newsqldemo container and make sure that we still have the database that we’d created earlier in the demo.

Once we get into the SQL shell, query the sys.databases system object. This will list all the databases of the Newsqldemo instance.

Summary

Docker containers are definitely a top choice and talking point for many developers for its rapid development and seamless deployment process.

Docker doesn’t give you a way of saying mount this guest file system into the host, preserving what was on the guest. It always chooses to mount the host file-system over the guest file-system.

You can also have multiple containers linked to this single volume in order to have them share a common storage resource. With Docker volumes, you can save data across multiple Docker containers or share data between several containers at the same time.

Table of contents

References

• Restore a SQL Server database in a Linux Docker container
• Yes to databases in containers – Microsoft SQL Server available on docker store
• microsoft/mssql-server-windows-developer

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021