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
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'
