SQLServer Docker: How do I backup & restore the data *volume*? [duplicate]

Question

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 $pwd\backup:/backup ubuntu bash -c "cd /whsqldata && tar xvf /backup/backup.tar --strip 1"
docker run --rm -v sqldata -v $pwd\backup:/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?!

Answer 1

OK, after reading the answers to How should I backup & restore docker named volumes I found out that my mistake was in how I mounted the volume. Instead of -v sqldata I have to write -v sqldata:/sqldata. Also changed some paths in my commands.

The completed commands are:

# Backup the data volume
docker run --rm \
       -v sqldata:/sqldata \
       -v $pwd\:/backup \
       ubuntu tar cvf /backup/backup.tar /sqldata

# Remove existing data volume (clear up old data, if exists)
docker volume rm sqldata

# Restore the data volume
docker run --rm \
       -v sqldata:/sqldata \
       -v $pwd\:/backup \
       ubuntu tar xvf /backup/backup.tar -C sqldata --strip 1