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

Docker engine 1.8+ on Linux/Mac/Windows Min 2 GB disk space and Min 2 GB RAM Open the Super User console 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


Understanding Backup and Restore operations in SQL Server Docker Containers

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


Understanding Backup and Restore operations in SQL Server Docker Containers

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.


Understanding Backup and Restore operations in SQL Server Docker Containers

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
Understanding Backup and Restore operations in SQL Server Docker Containers

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-outof the container using the docker cp command.

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

First, the container name, shackdemo1, followed by colon and then path of the backup file to copy. Second, the host path to copy the file.
Understanding Backup and Restore operations in SQL Server Docker Containers

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

ls l /tmp/SQLShackDemo.bak


Understanding Backup and Restore operations in SQL Server Docker Containers
Understanding Backup and Restore operations in SQL Server Docker Containers

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:
Understanding Backup and Restore operations in SQL Server Docker Containers

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.

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. 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
Understanding Backup and Restore operations in SQL Server Docker Containers

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


Understanding Backup and Restore operations in SQL Server Docker Containers
Data externalization

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

Let’s discuss the Docker’s solution that keeps the data safe acrosscontainers. Using Docker data volume (-v) option, it is that simple to share the data. During the SQL Server containercreation process, map to the SQL Server database file directoryusing v parameter.

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

#docker run -e'ACCEPT_EULA=Y' -e'MSSQL_SA_PASSWORD=thanVitha@2015' --name sqldemo v sqlservervolume:/var/opt/mssql -d microsoft/mssql-server-linux:2017-latest
Understanding Backup and Restore operations in SQL Server Docker Containers

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

#docker ps a
Understanding Backup and Restore operations in SQL Server Docker Containers

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

Now, we’ve created the Sqldemo container. Now, instead of storing database fileswithin 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.

#docker volume ls
Understanding Backup and Restore operations in SQL Server Docker Containers

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

#docker exec it sqldemo bash #/opt/mssql-tools/bin/sqlcmd U SA P thanVitha@2015 1>create database sqlvolumetestDB; 2>go
Understanding Backup and Restore operations in SQL Server Docker Containers
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 volu

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLDockerLinuxWindowsSQL ServerREST
代码区博客精选文章
分页:12
转载请注明
本文标题:Understanding Backup and Restore operations in SQL Server Docker Containers
本站链接:https://www.codesec.net/view/578176.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(87)