title: SQLServer Docker: How do I backup & restore the data *volume*?
body: <p>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 <code>docker-compose up / down</code>
  since the server uses a <strong>docker volume</strong>. The data disappears when I use <code>docker-compose down -v</code>. So <strong><em>this works as intended:</em></strong></p>
  
  <pre><code>services:
      sql:
          image: mcr.microsoft.com/mssql/server:2017-GA-ubuntu
          volumes:
           - sqldata:/var/opt/mssql
          ...
  
  volumes:
      sqldata:
          driver: local
          name: sqldata
  </code></pre>
  
  <p>Now I am trying to backup &amp; restore the database. I know the "normal" way, using the SQLServer directly. <strong><em>This works:</em></strong></p>
  
  <pre><code># 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
  </code></pre>
  
  <p>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!</p>
  
  <p>I found this:</p>
  
  <pre><code># Make sure the SQLServer is not writing/blocking any files.
  docker-compose stop sql
  
  # Backup &amp; Restore the sqldata volume.
  docker run --rm -v sqldata -v $pwd\backup:/backup ubuntu bash -c "cd /whsqldata &amp;&amp; tar xvf /backup/backup.tar --strip 1"
  docker run --rm -v sqldata -v $pwd\backup:/backup ubuntu bash -c "cd /whsqldata &amp;&amp; tar cvf /backup/backup.tar ."
  
  # Restart the SQLServer.
  docker-compose start sql
  </code></pre>
  
  <p>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. <strong>It looks like the backup.tar has no content.</strong>
  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:</p>
  
  <pre><code>docker run --rm -v sqldata -it ubuntu
  / # ls sqldata/ -a
  . ..
  / #
  </code></pre>
  
  <p>The SQLServer´s data persists. So it´s got to be saved <em>somewhere</em>, right? What am I missing?!</p>