Restore SQL Server backups using Docker

Photo by Rye Jessen

Docker has become an important piece of development for example we can recreate easily a development environment where we can restore SQL Server backups into a Docker container with a minimal effort.

In this post, you will learn how to:

  • Download a SQL Server Image into Docker.
  • Create a dockerfile.
  • Build a new image.
  • Restore SQL Server backups into a Docker container.
  • Connect to a docker container from a host.

The scenario is as follows: You need to quickly restore a database, create a user, and grant them permissions to access it.

In this example we are going to use Windows 10 Pro as host and an image in Linux with SQL Server 2017 on which a backup will be restored and a new image will be created. Then we will launch a container with the created image and we will be able to access the restored database from our application or from the SQL Management Studio.

Prerequisite

Keep in mind that Docker requires Hyper-V and it is only available in the PRO version of Windows 10. In addition, if you have Virtual Box, it is not compatible with Hyper-V, so you will not be able to run them simultaneously. If you want more detail check here. Below is required for this post.

  • Windows 10 Pro, Enterprise or Educational (Build 15063 or later).
  • Hyper-V.
  • Visual Studio Code (Optional, if you want you can use Notepad ++).
    • Add Docker extension
  • Docker Community v 2.0.0 or higher.
  • At least 2 GB although they recommend 3.25GB in RAM for the container.
  • SQL Server Management Studio

The commands will be executed using PowerShell in Administrator mode.

Setting up Docker

Although Docker allows you to create containers on Linux and Windows for SQL Sever there is something very important, an image of SQL Server Developer for a Windows container has a size of 14GB, one for Linux is around 1.4GB. However, there are other versions of SQL Server for Windows, such as Express, which can be at 6GB. In this post we will work with SQL Server 2017. The first step will be to enable the Linux containers in Windows in the Docker graphic interface, then it will be to download the base image that contains the SQL Server in Linux.

docker pull FROM microsoft/mssql-server-linux:2017-CU12

Creating the file structure

The file structure for this example is as follows.

In the DOCKER-SQL-SERVER-LINUX folder we will create another folder to add our backup to restore, in this example we will use AdventureWorks2017.bak. In the src folder is the Restore.sh which will have the commands to configure the instance of SQL Server, parameters such as assigned RAM, key and the execution of the RestoreDb.sql script which has the commands for restoration, user creation and assignment of permissions to the restored base. Finally at the root are the files dockerfile and README.MD; the first with the commands to create the new image and the second with the basic project documentation.

Dockerfile

The dockerfile file, as will be seen below, uses the base image “microsoft / mssql-server-linux”, then defines the working directories to which the backup and the RestoreDb.sql and Restore.sh files will be copied. Finally, execute permissions are assigned to Restore.sh and execute the command sent as parameter RestoreDb.sql.

FROM microsoft/mssql-server-linux:2017-CU12

RUN mkdir /work

COPY /backups/AdventureWorks2017.bak /work

COPY /src/RestoreDb.sql /work

COPY /src/Restore.sh /work

WORKDIR /work

RUN chmod 755 Restore.sh

RUN ./Restore.sh RestoreDb.sql

Script to restore database – RestoreDb.sql

USE master
GO

PRINT 'Restoring Adventure works'
 -------------------------------------------------
--> Restoring Adventure works 
-------------------------------------------------

RESTORE DATABASE AdventureWorks2017
FROM DISK =  N'/work/AdventureWorks2017.bak'
WITH FILE = 1,
     MOVE N'AdventureWorks2017'
     TO  N'/var/opt/mssql/data/AdventureWorks2017.mdf',
     MOVE N'AdventureWorks2017_log'
     TO  N'/var/opt/mssql/data/AdventureWorks2017_log.ldf',
     NOUNLOAD,
     STATS = 5;

GO

-------------------------------------------------
--> Adding user AdventureUser 
-------------------------------------------------

USE master;
GO
CREATE LOGIN AdventureUser
WITH PASSWORD = N'Adventure.@2018',
     DEFAULT_DATABASE = AdventureWorks2017
GO


-------------------------------------------------
--> Adding permissions to AdventureUser
-------------------------------------------------

USE AdventureWorks2017
GO
CREATE USER AdventureUser FOR LOGIN AdventureUser
GO
USE AdventureWorks2017
GO
ALTER ROLE db_owner ADD MEMBER AdventureUser
GO

Setting up SQL Server instance – Restore.sh

In this file we have all the configurations that we want to apply to our instance of SQL Server, such as assigning a key, configuring the memory limit, executing a restore script. The password assigned to the SA user is Sa.@2018Password . If you want to see more configuration commands enter here.

echo "Setting Environment variables."
export ACCEPT_EULA=Y
export SA_PASSWORD=Sa.@2018Password

echo "Environment variables set."
echo "Starting SqlServr"
/opt/mssql/bin/sqlservr &
sleep 60 | echo "Waiting for 60s to start Sql Server"

echo "Setting RAM to 2GB usage."
/opt/mssql/bin/mssql-conf set memory.memorylimitmb 2048

echo "Restarting to apply the changes."
systemctl restart mssql-server.service

echo "Restoring DB."
/opt/mssql-tools/bin/sqlcmd -U sa -P $SA_PASSWORD -i $1

echo "DB restored."
echo "Deleting backup files."
rm -rf /work/*.bak

Building a new image

At this point, you must have already downloaded the base image of SQL Server for Linux and created the structure as seen in previous steps. Now we will execute the necessary commands to execute the container and be able to connect through SQL Server Management to it. The first thing we should do is locate ourselves in the folder where the dockerfile file is located. Then run the following command using a PowerShell console:

docker build . -t sql-server-linux

With this last command we have created a new image called sql-server-linux, this image already contains the restored database. When consulting the following command, we must display the 2 images. The first sql-server-linux image that has been created from the second mssql-server-linux.

docker images

Run a container

Now we already have an image that has restored the Adventure Works 2017 database and we can create a container and carry out the tests that we want.

docker run -d -p 1500:1433 --name sql-server-container sql-server-linux

To validate if everything is ok we can execute the following command to display the created containers.

docker ps -a

Accessing the database

Now you can connect from SQL Server Management to the database with the parameters localhost 1500 and the user AdventureUser with the password AdventureWorks2017.

Finally, if you have restore SQL Server backups into a container, remember to stop it to free up resources on your computer.

docker stop sql-server-container

Additional resources

View or download sample code for this post.


Leave a Reply

Your email address will not be published. Required fields are marked *