Skip to content

Instantly share code, notes, and snippets.

@johanvergeer

johanvergeer/create_and_restore_backup.md

Last active October 9, 2020 18:22
Show Gist options
  • Save johanvergeer/e55b12c2a2916eec2c049a0cf2aba811 to your computer and use it in GitHub Desktop.
Save johanvergeer/e55b12c2a2916eec2c049a0cf2aba811 to your computer and use it in GitHub Desktop.
Download ZIP
Create and replace database backup in Docker
Raw
create_and_restore_backup.md

Create and restore a database backing using Docker

This script is intended to demonstrate how a backup can be created and restored using Docker containers. The image we'll use is SQL Server 2017

The scripts are baed on the official Microsoft documentation

Create a new container and volume

Create a new container from the mssql-server-linux image

docker container run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=MyStrong!Passw0rd' \
   --name 'sql1' -p 1433:1433 \
   -v sql1data:/var/opt/mssql \
   -d microsoft/mssql-server-linux:2017-latest

Confirm the container is running

$ docker container ls
CONTAINER ID        IMAGE                                      COMMAND                  CREATED             STATUS              PORTS                    NAMES
8d1e5fdca39e        microsoft/mssql-server-linux:2017-latest   "/opt/mssql/bin/sqls…"   2 seconds ago       Up 4 seconds        0.0.0.0:1433->1433/tcp   sql1

Confirm the volume is created

$ docker volume ls
DRIVER              VOLUME NAME
local               sql1data

OPTIONALLY: get the example backup file from the Microsoft Github repository

curl -L -o wwi.bak 'https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak'

Copy the backup file into the container

docker container exec -it sql1 mkdir /var/opt/mssql/backup
docker cp wwi.bak sql1:/var/opt/mssql/backup

List out logical file names and paths inside the container

$ docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost \
   -U SA -P 'MyStrong!Passw0rd' \
   -Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/wwi.bak"' \
   | tr -s ' ' | cut -d ' ' -f 1-2

LogicalName   PhysicalName
------------------------------------------
WWI_Primary   D:\Data\WideWorldImporters.mdf
WWI_UserData   D:\Data\WideWorldImporters_UserData.ndf
WWI_Log   E:\Log\WideWorldImporters.ldf
WWI_InMemory_Data_1   D:\Data\WideWorldImporters_InMemory_Data_1

Restore the database

$ docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P 'MyStrong!Passw0rd' \
   -Q 'RESTORE DATABASE WideWorldImporters FROM DISK = "/var/opt/mssql/backup/wwi.bak" WITH MOVE "WWI_Primary" TO "/var/opt/mssql/data/WideWorldImporters.mdf", MOVE "WWI_UserData" TO "/var/opt/mssql/data/WideWorldImporters_userdata.ndf", MOVE "WWI_Log" TO "/var/opt/mssql/data/WideWorldImporters.ldf", MOVE "WWI_InMemory_Data_1" TO "/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1"'

Processed 1464 pages for database 'WideWorldImporters', file 'WWI_Primary' on file 1.
Processed 53096 pages for database 'WideWorldImporters', file 'WWI_UserData' on file 1.
Processed 33 pages for database 'WideWorldImporters', file 'WWI_Log' on file 1.
Processed 3862 pages for database 'WideWorldImporters', file 'WWI_InMemory_Data_1' on file 1.
Converting database 'WideWorldImporters' from version 852 to the current version 869.
Database 'WideWorldImporters' running the upgrade step from version 852 to version 853.
Database 'WideWorldImporters' running the upgrade step from version 853 to version 854.
Database 'WideWorldImporters' running the upgrade step from version 854 to version 855.
Database 'WideWorldImporters' running the upgrade step from version 855 to version 856.
Database 'WideWorldImporters' running the upgrade step from version 856 to version 857.
Database 'WideWorldImporters' running the upgrade step from version 857 to version 858.
Database 'WideWorldImporters' running the upgrade step from version 858 to version 859.
Database 'WideWorldImporters' running the upgrade step from version 859 to version 860.
Database 'WideWorldImporters' running the upgrade step from version 860 to version 861.
Database 'WideWorldImporters' running the upgrade step from version 861 to version 862.
Database 'WideWorldImporters' running the upgrade step from version 862 to version 863.
Database 'WideWorldImporters' running the upgrade step from version 863 to version 864.
Database 'WideWorldImporters' running the upgrade step from version 864 to version 865.
Database 'WideWorldImporters' running the upgrade step from version 865 to version 866.
Database 'WideWorldImporters' running the upgrade step from version 866 to version 867.
Database 'WideWorldImporters' running the upgrade step from version 867 to version 868.
Database 'WideWorldImporters' running the upgrade step from version 868 to version 869.
RESTORE DATABASE successfully processed 58455 pages in 2.690 seconds (169.767 MB/sec).

At this point the database is restored

Make some changes in the database

$ docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P 'MyStrong!Passw0rd' \
   -Q 'UPDATE WideWorldImporters.Warehouse.StockItems SET StockItemName="USB missile launcher (Dark Green)" WHERE StockItemID=1; SELECT StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems WHERE StockItemID=1'
(1 rows affected)
StockItemID StockItemName
----------- ----------------------------------------------------------------------------------------------------
          1 USB missile launcher (Dark Green)

(1 rows affected)

Create a new backup

$ docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P 'MyStrong!Passw0rd' \
   -Q "BACKUP DATABASE [WideWorldImporters] TO DISK = N'/var/opt/mssql/backup/wwi_2.bak' WITH NOFORMAT, NOINIT, NAME = 'WideWorldImporters-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
Processed 1488 pages for database 'WideWorldImporters', file 'WWI_Primary' on file 1.
Processed 53096 pages for database 'WideWorldImporters', file 'WWI_UserData' on file 1.
70 percent processed.
Processed 3865 pages for database 'WideWorldImporters', file 'WWI_InMemory_Data_1' on file 1.
Processed 76 pages for database 'WideWorldImporters', file 'WWI_Log' on file 1.
100 percent processed.
BACKUP DATABASE successfully processed 58525 pages in 5.194 seconds (88.029 MB/sec).

Copy backup out of the container

First we copy the backup out of the container

docker cp sql1:/var/opt/mssql/backup/wwi_2.bak wwi_2.bak

And we verify the copy is stored on our host

$ ls -l wwi*
-rw-r--r--  1 user  staff  127056896 Jun 17 09:38 wwi.bak
-rw-r-----  1 user  staff  482086912 Jun 17 15:48 wwi_2.bak

Now the backup can be stored on another server or other storage medium.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment