Direct naar content

SQL Server running on Docker part 2 Backup and Restore

In the first part of this series of two, we explained how to run SQL Server on Docker. The end result was a Docker container and a storage volume containing a SQL Server instance. In this blog, Taco Zoetemelk continues working with this Docker container and shows how to backup and restore.

To manage the instance, it was even possible to connect to the SQL Server instance with Management Studio. I will continue working with this Docker container, so if you want to try out the actions in this blog, I recommend that you first perform the steps from Part 1. All actions are performed from Docker shell, so not through Management Studio.

Current situation

Preparations

I start by starting the container. If it is not already running, do the following first:
Check what is currently running:

$ docker ps

Check which containers are available to start:

$ docker ps -a

Check if the volume is available:

$ docker volume ls

Starting the docker container

$ docker start some-mssql

Check that the container has started properly:

$ docker ps

Check that the previously created database still exists and that there are still records in the created table:

$ docker exec -it some-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P VeryStrongPassword@2019 -d DockerTest -Q "select count(*) from DockerTestTable"

Empty test table and populate with a little more data

docker exec -it some-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P VeryStrongPassword@2019 -d DockerTest -Q "declare @num int; set @num =1; while @num < 10001 begin; insert into [DockerTest].[dbo].[DockerTestTable] values (@num, 'Testdata ' + cast(@num as nvarchar) ); set @num = @num + 1 ; end ;"

Create backup directory on the storage volume so that the backups remain available, too, in case the container stops.

docker exec -it some-mssql /bin/mkdir -p /var/opt/mssql/sql_backup

Backup

Backups can be made with the regular backup commands, just as queries can be run with the sqlcmd tool. I start with a simple full backup to the previously created backup directory.

$ docker exec -it some-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P VeryStrongPassword@2019 -d DockerTest -Q "BACKUP DATABASE [DockerTest] TO DISK = N'/var/opt/mssql/sql_backup/DockerTest_Full.bak' WITH NOFORMAT, NOINIT, NAME = N'DockerTest-Full Database Backup', COMPRESSION, STATS = 10"

If you regularly work with SQL Server, you will see that the backup command is no different than if you were to do it from Management Studio in T/SQL.

If I look in the created backup directory, you will see that a backup file has now been created there.

$ docker exec -it some-mssql /bin/ls -hl /var/opt/mssql/sql_backup

Restore

$ docker exec -it some-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P VeryStrongPassword@2019 -d DockerTest -Q " declare @num int; set @num =1; while @num < 500 begin; delete from [DockerTest].[dbo].[DockerTestTable] where id = (SELECT CAST(RAND()*10000 AS INT)); set @num = @num + 1 ; end ;"

...First, I delete some (random) data from the table, to simulate a user error. With this query, 500 random numbers are generated and the record with that id is deleted.l

When I then look in the table there are only 9,516 records left in the table (numbers may vary because random numbers may occur more often)

$ docker exec -it some-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P VeryStrongPassword@2019 -d DockerTest -Q "select count(*) from [DockerTest].[dbo].[DockerTestTable]"

Fortunately, I made a backup and it can be restored.

$ docker exec -it some-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P VeryStrongPassword@2019 -d DockerTest -Q "USE [master] ; ALTER DATABASE [DockerTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ; BACKUP LOG [DockerTest] TO DISK = N'/var/opt/mssql/data/DockerTest_LogBackup_2019-11-01_09-49-23.bak' WITH NOFORMAT, NOINIT, NAME = N'DockerTest_LogBackup_2019-11-01_09-49-23', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5 ;RESTORE DATABASE [DockerTest] FROM DISK = N'/var/opt/mssql/sql_backup/DockerTest_Full.bak' WITH FILE = 1, MOVE N'DockerTest' TO N'/var/opt/mssql/data/DockerTest.mdf', MOVE N'DockerTest_log' TO N'/var/opt/mssql/data/DockerTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5 ;ALTER DATABASE [DockerTest] SET MULTI_USER ; "