2

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

1

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.

  • thanks but I already used a mounted volume...see updated question for more details – Martin Guth May 10 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.