SQL Server Demo Environment on Docker Desktop (Win 10): DB storage und upgrade paths I would like to run SQL Server 2019 on my work as well as my private machine (both running Windows 10 Pro). Thus I thought Docker Desktop would be a nice option.

However I would like to keep the docker image itself minimal (just SQL Server and system databases) and store my demo databases elsewhere. I have tried to accomplish this using a data volume mounted from the host using the following command line switch.

-v C:\SQL:/sql


CREATE DATABASE does work using FILENAME "C:\sql..."

    CREATE DATABASE [test] CONTAINMENT = NONE ON  PRIMARY 
( NAME = N'test', FILENAME = N'C:\sql\test.mdf' ,...


ATTACH does work  using Database File Location "/sql/..."

USE [master]
GO
CREATE DATABASE [CustomerOrders] ON 
--( FILENAME = N'/sql/mssql/data/CustomerOrders.mdf' ), --generated by ssms -&gt; wrong
(FILENAME = N'C:\SQL\mssql\data\CustomerOrders.mdf'),
( FILENAME = N'C:\SQL\mssql\data\CustomerOrders_log.ldf' )
 FOR ATTACH
GO


However RESTORE does only work with destination /var/opt/mssql and NOT with
destination /sql/ or C:\sql

Try 1 

      USE [master]
    RESTORE DATABASE [Credit] FROM  DISK = N'/sql/backup/CreditBackup100.bak' 
WITH  FILE = 1,  MOVE N'CreditData' TO N'C:\SQL\CreditData.mdf',  
MOVE N'CreditLog' TO N'C:\SQL\CreditLog.ldf',  NOUNLOAD,  STATS = 5, REPLACE


Try 2

USE [master]
RESTORE DATABASE [Credit] FROM  DISK = N'/sql/backup/CreditBackup100.bak' 
WITH  FILE = 1,  MOVE N'CreditData' TO N'/sql/CreditData.mdf',  
MOVE N'CreditLog' TO N'/sql/CreditLog.ldf',  NOUNLOAD,  STATS = 5, REPLACE

GO


Error Messages


  
    Msg 3634, Level 16, State 1, Line 2 The operating system returned the error '2(The system cannot find the file specified.)' while
    attempting 'RestoreContainer::ValidateTargetForCreation' on
    '/sql/CreditData.mdf'. Msg 3156, Level 16, State 5, Line 2 File
    'CreditData' cannot be restored to '/sql/CreditData.mdf'. Use WITH
    MOVE to identify a valid location for the file. Msg 3634, Level 16,
    State 1, Line 2 The operating system returned the error '2(The system
    cannot find the file specified.)' while attempting
    'RestoreContainer::ValidateTargetForCreation' on '/sql/CreditLog.ldf'.
    Msg 3156, Level 16, State 5, Line 2 File 'CreditLog' cannot be
    restored to '/sql/CreditLog.ldf'. Use WITH MOVE to identify a valid
    location for the file. Msg 3119, Level 16, State 1, Line 2 Problems
    were identified while planning for the RESTORE statement. Previous
    messages provide details. Msg 3013, Level 16, State 1, Line 2 RESTORE
    DATABASE is terminating abnormally.
  


This one works (but is not what I want, as it is stored inside the docker image then):

USE [master]
RESTORE DATABASE [Credit] FROM  DISK = N'/sql/backup/CreditBackup100.bak' 
WITH  FILE = 1,  MOVE N'CreditData' TO N'/var/opt/mssql/CreditData.mdf',  
MOVE N'CreditLog' TO N'/var/opt/mssql/CreditLog.ldf',  NOUNLOAD,  STATS = 5, REPLACE


Another big difference is file growth on database files stored within the container and on files on the volume (resp. the host).
I created a test database with a 8 MB mdf file  in /var/opt/mssql/data and in /sql (my data volume). Afterwards I issued the following command

ALTER DATABASE [test2] MODIFY FILE ( NAME = N'test2', SIZE = 1048576KB )


This grows the file to 1 GB. In the location /var/opt/mssql/data it finished instantely while the resize operation took 8 seconds on the data volume /sql.

I tested for Instant File Initialization with traceflags 3004 and 3605 but didn't see any output regarding nulling the mdf file out on the data volum /sql.

Thanks in advance for your help

Martin
Performance of SQL Server using shares to host data files is almost always poor, regardless of the underlying storage subsystem. You should be mounting a host directory as a volume in your docker container to get better performance. See this link for more information.