Docker for Windows: SQL Server Database on persistent Volume with Windows-Container

On Docker for Windows and working with windows containers, I cannot get my persistent volume to work on the main database directory of the windows container. This would be C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA How can I get the benefits of persistent volumes for databases without having to mess with backups and restores into the mssql-server-container?

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.

In SQL Server for linux containers this simply works, you can connect the persistent volume to /var/opt/mssql and have your database persistent.

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.

docker run -d -e sa_password=<Password> -e ACCEPT_EULA=Y -v "C:\mylocalfolder:C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA" microsoft/mssql-server-windows-developer

The error message is 'failure in a Windows system call: the virtual computer or container was shutdown unexpectetly. (0xc0370106)

Workaround 1

connect persistent volume to another location like c:\mydata to prevent the error message from above. Then get the database connected to the server while not using the standard database folder.

Extract database .bak file, so there are mdf and log files

--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

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 but this procedure only takes nanoseconds to complete!

CREATE DATABASE StackoverflowIsGreat
ON (FILENAME = 'c:\mydata\extractedDb.mdf'),
(FILENAME = 'c:\mydata\extractedLog.ldf')
FOR ATTACH;

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):

CREATE DATABASE StackoverflowIsGreat
ON (FILENAME = 'c:\mydata\extractedDb.mdf'),
(FILENAME = 'c:\mydata\extractedLog.ldf')
FOR ATTACH;

Workaround 2

the -attach_dbs parameter seems to work the same way. Docker run:

docker run -p 1433:1433 --name mssql-dev -e sa_password=<yourpassword> -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

or if you prefer Docker-Compose, it is a little bit tricky. I had to omit the leading and closing ' outside of the brackets and replace the double quotation marks inside the brackets with ' to make it work.

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":"<yourDbName>","dbFiles":["C:\\<your>\\path\\database.mdf","C:\\your\\path\\databaselog.ldf"]}]
volumes:
  mssql-dev-data:

It seems this question can be answered with workaround 1 and 2 from above.

1. Connect persistent volume to another location like c:\mydata to prevent the error message from above. Then get the database connected to the server while not using the standard database folder.
2. Extract database .bak file, so there are mdf and log files

--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

3. Attach the database to the server in one of the following three ways:

Docker run example:

docker run -p 1433:1433 --name mssql-dev -e sa_password=<yourpassword> -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

if you prefer Docker-Compose, it is a little bit tricky. I had to omit the leading and closing ' outside of the brackets and replace the double quotation marks inside the brackets with ' to make it work. Example for docker-compose:

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":"<yourDbName>","dbFiles":["C:\\<your>\\path\\database.mdf","C:\\your\\path\\databaselog.ldf"]}]
volumes:
  mssql-dev-data:

Or attach DB with SQL Command

CREATE DATABASE StackoverflowIsGreat
ON (FILENAME = 'c:\mydata\extractedDb.mdf'),
(FILENAME = 'c:\mydata\extractedLog.ldf')
FOR ATTACH;