DATABASE BACKUP AND RESTORE IN DOCKER

              

Backup and restore of  SQL Database in a Docker container

Prerequisites

• Docker Engine 1.8+ on any supported Linux distribution or Docker for Mac/Windows. 
• Minimum of 2 GB of disk space
• Minimum of 2 GB of RAM

STEPS

1) Pull the SQL Server 2017 Linux container image from Docker Hub

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

2) To run the container image with Docker, you can use the following command:

sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' \

   --name 'sql1' -p 1401:1433 \

   -v sql1data:/var/opt/mssql \

   -d microsoft/mssql-server-linux:2017-latest

3) To view your Docker containers

   

    sudo docker ps -a

If the STATUS column shows a status of Up, then SQL Server is running in the container and listening on the port specified in the PORTS column.

4) Change SA password

   sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \

   -S localhost -U SA -P '<YourStrong!Passw0rd>' \

   -Q 'ALTER LOGIN SA WITH PASSWORD="<YourNewStrong!Passw0rd>"'

5)  Copy a Backup file into container 

     5.1) Backup folder create 

   sudo docker exec -it sql1 mkdir /var/opt/mssql/backup

    

     

   5.2) Next, download the WideWorldImporters-Full.bak file to your host machine

    cd ~

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

           

     

  5.3) Use docker cp to copy the backup file into the container in the /var/opt/mssql/backup directory.

sudo docker cp wwi.bak sql1:/var/opt/mssql/backup

  6) Restore Database 

6.1) Run sqlcmd inside the container to list out logical file names and paths inside the backup. This is done with the RESTORE FILELISTONLY Transact-SQL statement.

sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost \

   -U SA -P '<YourNewStrong!Passw0rd>' \

   -Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/wwi.bak"' \

   | tr -s ' ' | cut -d ' ' -f 1-2

6.2) Call the RESTORE DATABASE command to restore the database inside the container. Specify new paths for each of the files in the previous step.

sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \

   -S localhost -U SA -P '<YourNewStrong!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"'

6.3) Verify the database 

sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \

   -S localhost -U SA -P '<YourNewStrong!Passw0rd>' \

   -Q 'SELECT Name FROM sys.Databases'

    7)   Make a change in database 

     7.1) Run a query to view the top 10 items in database

     sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \

   -S localhost -U SA -P '<YourNewStrong!Passw0rd>' \

   -Q 'SELECT TOP 10 StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems ORDER BY StockItemID'

  7.2)   Run a query to update 

   sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \

   -S localhost -U SA -P '<YourNewStrong!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'

8)     Create a new backup

8.1) We have  restored  database into a container, you might also want to regularly create database backups inside the running container. The steps follow a similar pattern to the previous steps but in reverse.

sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \

   -S localhost -U SA -P '<YourNewStrong!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"

8.2) Next, copy the backup file out of the container and onto your host machine. 

cd ~

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

ls -l 

9)    Use the persisted data

in addition to taking database backups for protecting your data, you can also use data volume containers. The sql1data data volume container persists the /var/opt/mssql data even after the container is removed. The following steps completely remove the sql1 container and then create a new container, sql2, with the persisted data.

9.1)  Stop and remove SQL1 container 

        sudo docker stop sql1 

        sudo docker rm sql1

        

9.2) Create a new container, sql2, and reuse the sql1data data volume container.

sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' \

   --name 'sql2' -e 'MSSQL_PID=Developer' -p 1401:1433 \

   -v sql1data:/var/opt/mssql -d microsoft/mssql-server-linux:2017-latest

    

9.3) The Wide World Importers database is now in the new container. Run a query to verify the previous change you made.

sudo docker exec -it sql2 /opt/mssql-tools/bin/sqlcmd \

   -S localhost -U SA -P '<YourNewStrong!Passw0rd>' \

   -Q 'SELECT StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems WHERE StockItemID=1'