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