title: Docker for Windows: SQL Server Database on persistent Volume with Windows-Container
body: <p>On Docker for Windows and working with <strong>windows containers</strong>, I cannot get my persistent volume to work on the main database directory of the windows container. This would be <code>C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA</code>
  How can I get the benefits of persistent volumes for databases without having to mess with backups and restores into the mssql-server-container?</p>
  
  <p>This may be because of the data directory having the master- and system-dbs stored inside this folder where I try to mount the persistent volume.</p>
  
  <p>In SQL Server for linux containers this simply works, you can connect the persistent volume to <code>/var/opt/mssql</code> and have your database persistent.</p>
  
  <p>I know I can recover a database from a backup into the container, but this has two major drawbacks: I have to have a big container size because I am working with a big database. So I extended the 20 GB limit of the container to 60 GB but... rebuilding the database each time from a backup is time consuming. 
  The second drawback is, if the mssql-dev container is killed, the database is lost, too. Any work on this database is then gone. This would be different if the database could reside on the persistent volume.</p>
  
  <pre><code>docker run -d -e sa_password=&lt;Password&gt; -e ACCEPT_EULA=Y -v "C:\mylocalfolder:C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA" microsoft/mssql-server-windows-developer
  </code></pre>
  
  <p>The error message is 'failure in a Windows system call: the virtual computer or container was shutdown unexpectetly. (0xc0370106)</p>
  
  <h2>Workaround 1</h2>
  
  <p>connect persistent volume to another location like
  <code>c:\mydata</code> to prevent the error message from above. 
  Then get the database connected to the server while not using the standard database folder.</p>
  
  <p>Extract database .bak file, so there are mdf and log files</p>
  
  <pre class="lang-sql prettyprint-override"><code>--Get the name of your DB
  RESTORE FILELISTONLY
  FROM DISK = 'c:\mydata'
  GO
  --do the extraction of the bak file to certain folder
  RESTORE DATABASE mydatabase
  FROM DISK = 'c:\mydata'
  WITH REPLACE,
  MOVE 'mydatabase' TO 'c:\mydata\extractedDb.mdf',
  MOVE 'mydatabase_log' TO 'c:\mydata\extractedLog.ldf'
  GO
  </code></pre>
  
  <p>With this done you should now have your database files ready on your persistent volume. Now attach the database to the server. This has to be done by creating a new db <strong>but this procedure only takes nanoseconds to complete!</strong></p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE DATABASE StackoverflowIsGreat
  ON (FILENAME = 'c:\mydata\extractedDb.mdf'),
  (FILENAME = 'c:\mydata\extractedLog.ldf')
  FOR ATTACH;
  </code></pre>
  
  <p>Now the database is safe in a persistent volume. If the db-server container goes down or is rebuild, you simply run this last statement again (or even better implement it in your docker-compose or dockerfile):</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE DATABASE StackoverflowIsGreat
  ON (FILENAME = 'c:\mydata\extractedDb.mdf'),
  (FILENAME = 'c:\mydata\extractedLog.ldf')
  FOR ATTACH;
  </code></pre>
  
  <h2>Workaround 2</h2>
  
  <p>the -attach_dbs parameter seems to work the same way. 
  Docker run:</p>
  
  <pre><code>docker run -p 1433:1433 --name mssql-dev -e sa_password=&lt;yourpassword&gt; -e ACCEPT_EULA=Y -e attach_dbs="[{'dbName':'PowerSlide_SQLDB','dbFiles':['C:\\your\\path\\database.mdf','C:\\sqldata\\databaselog.ldf']}]" -v "d:\sqldata:C:\sqldata" microsoft/mssql-server-windows-developer
  </code></pre>
  
  <p>or if you prefer Docker-Compose, it is a little bit tricky. I had to omit the leading and closing <code>'</code> outside of the brackets and replace the double quotation marks inside the brackets with <code>'</code> to make it work.</p>
  
  <pre><code>version: '3.2'
  services:
    mssql-dev:
      container_name: mssql-dev
      image: 'microsoft/mssql-server-windows-developer'
      volumes:
        - "d:\\sqldata:C:\\sqldata"
      ports:
        - "1433:1433"
      restart: always
      environment:
        - "ACCEPT_EULA=Y"
        - "sa_password=yourpassword"
        - attach_dbs=[{"dbName":"&lt;yourDbName&gt;","dbFiles":["C:\\&lt;your&gt;\\path\\database.mdf","C:\\your\\path\\databaselog.ldf"]}]
  volumes:
    mssql-dev-data:
  </code></pre>