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 ; "
Now when I look at the records in the table, they are again the original 10000 records:
$ 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]"
“offsite back-up”
Normally, you would ensure that a backup file is also available outside your server. By separating the container from the storage volume, we’ve actually already accomplished this. Still, I’m going to show you how to get the backup files out of the container or storage volume. I’m also going to show you how to put the backup files in a shared folder so that they are available on the host and in the container.
Retrieve backup files from the container
To retrieve files from the container so that they become available on the host, within Docker there is the cp command. This allows files to be exchanged between the host and the container. Let’s start by retrieving the previously created backup to the local host.
I have created a directory on my host computer c:SQL_BACKUP where the backup file can reside. The command below will transfer the previously created file to my local host:
$ docker cp some-mssql:/var/opt/mssql/sql_backup/DockerTest_Full.bak "c:SQL_BACKUP"
This command is structured as follows:
cp: the docker command
some-mssql:/var/opt/mssql/sql_backup/DockerTest_Full.bak: container + : + path + file
”C:SQL_BACKUP”: lokale path
Because I am using Windows and because Docker uses : as a separator, I put my local path in quotes.
When I run this command, the file DockerTest_Full.bak in location /var/opt/mssql/sql_backup in container some-mssql will be retrieved and placed in C:SQL_BACKUP
Files uploaden naar de container
The other way around, of course, is also highly desirable: that you can make your backup available in the container, if it is local. The cp command can be used for this as well, but the other way around:
$ docker cp "c:SQL_BACKUPDockerTest_Full.bak" some-mssql:/var/opt/mssql/sql_backup/
The command is basically the same as the previous command and consists of an original file (“c:SQL_BACKUPDockerTest_Full.bak”) and a location (/var/opt/mssql/sql_backup) on the container (some-mssql) where the file should be placed.
Want to know more?
This is one of the many things Docker has to offer. Did you get excited and want to know more? Do you need help designing with and implementing Docker and or SQL Server? Feel free to contact us.