I have a MS SQLServer 2017 Linux Docker container running with docker-compose. (Working on
a Windows host.)
The server is running, I added data, and this data is persistent across multiple
docker-compose up / down
since the server uses a docker volume. The data disappears when I use
docker-compose down -v. So this works as intended:
services: sql: image: mcr.microsoft.com/mssql/server:2017-GA-ubuntu volumes: - sqldata:/var/opt/mssql ... volumes: sqldata: driver: local name: sqldata
Now I am trying to backup & restore the database. I know the “normal” way, using the SQLServer directly. This works:
# Restore a backup inside the container volume docker exec -it sql mkdir /var/opt/mssql/backup docker cp .Test.bak sql:/var/opt/mssql/backup sqlcmd -S 127.0.0.1,1433 -U sa -P Secr3tSA_Passw0rd -H 127.0.0.1,1433 -Q "RESTORE DATABASE [Test] FROM DISK='/var/opt/mssql/backup/Test.bak' WITH REPLACE" # Backup a database inside the container volume, then copy to local file docker exec sql rm -rf /var/opt/mssql/backup/Test.bak sqlcmd -S 127.0.0.1,1433 -U sa -P Secr3tSA_Passw0rd -H 127.0.0.1,1433 -Q "BACKUP DATABASE [Test] TO DISK='/var/opt/mssql/backup/Test.bak'" docker cp sql:/var/opt/mssql/backup/Test.bak .Test.bak
Now I was thinking, maybe there is a better way than to put the SA password into a BAT file
and hand that out to my customers and service technicians. Simply grabbing a copy of the volume
should do the trick!
I found this:
# Make sure the SQLServer is not writing/blocking any files. docker-compose stop sql # Backup & Restore the sqldata volume. docker run --rm -v sqldata -v $pwdbackup:/backup ubuntu bash -c "cd /whsqldata && tar xvf /backup/backup.tar --strip 1" docker run --rm -v sqldata -v $pwdbackup:/backup ubuntu bash -c "cd /whsqldata && tar cvf /backup/backup.tar ." # Restart the SQLServer. docker-compose start sql
This creates the expected backup.tar in my user directory… But it is suspiciously small! And after the
restore, the SQLServer cannot connect to the database. It looks like the backup.tar has no content.
But on closer inspection, so has my sqldata volume! It is empty!? When I start a bash that mounts that
same volume, I can see the directory but there is nothing in it:
docker run --rm -v sqldata -it ubuntu / # ls sqldata/ -a . .. / #
The SQLServer´s data persists. So it´s got to be saved somewhere, right? What am I missing?!