Prashanth Jayaram

Understanding Backup and Restore operations in SQL Server Docker Containers

July 12, 2018 by

In this 17th 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

Database Backup and Restore process in SQL Server – series intro
An overview of the process of SQL Server backup-and-restore
Understanding the SQL Server Data Management Life Cycle
Understanding SQL Server database recovery models
Understanding SQL Server Backup Types
Backup and Restore (or Recovery) strategies for SQL Server database
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
Understanding Database snapshots vs Database backups in SQL Server
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
Smart database backup in SQL Server 2017
How to perform a Page Level Restore in SQL Server
Backup Linux SQL Server databases using PowerShell and Windows task scheduler
SQL Server Database backup and restore operations using the Cloud
Tail-Log Backup and Restore in SQL Server
SQL Server Database Backup and Restore reports
Database Filegroup(s) and Piecemeal restores in SQL Server
In-Memory Optimized database backup and restore in SQL Server
Understanding Backup and Restore operations in SQL Server Docker Containers
Backup and Restore operations with SQL Server 2017 on Docker containers using SQL Operations Studio
Interview questions on SQL Server database backups, restores and recovery – Part I
Interview questions on SQL Server database backups, restores and recovery – Part II
Interview questions on SQL Server database backups, restores and recovery – Part III
Interview questions on SQL Server database backups, restores and recovery – Part IV

References


See more

To manage SQL Server backups, consider ApexSQL Backup, a tool that offers automation of backup, restore, and log shipping jobs, stores details of all backup activities and enables easy cross server backup management and maintenance.


Prashanth Jayaram

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
Prashanth Jayaram
Backup and restore, Docker

About 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

1,543 Views