Restore database in docker container

Question

Getting an error below when restoring a AdventureWorks2017 database within a docker container. Running SQL Server 2019 CTP 2.0 (mcr.microsoft.com/mssql/server:vNext-CTP2.0-ubuntu) Both backup and target data volume are persisted. No problems creating new database. Checked the paths and they are correct. Do not have any problems when restoring using 2017-latest docker image.

Anybody else have this issue with 2019-CTP2, workarounds?

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

to create container.

$datapath = "D:\Foo";
$logpath = "D:\Foo";
$backuppath = "D:\Foo";
$pass = ":-)"

$ct = (docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=$pass" `
    -e "MSSQL_PID=Developer" -p 2017:1433 `
    -e "MSSQL_TCP_PORT=1433" `
    -v ${datapath}:/var/opt/mssql/data `
    -v ${logpath}:/var/opt/mssql/log `
    -v ${backuppath}:/var/opt/mssql/backup `
    -e "MSSQL_BACKUP_DIR=/var/opt/mssql/backup" `
    -e "MSSQL_DATA_DIR=/var/opt/mssql/data" ` 
    -e "MSSQL_LOG_DIR=/var/opt/mssql/log" `
    -d mcr.microsoft.com/mssql/server:vNext-CTP2.0-ubuntu)

Restore command.

RESTORE DATABASE [AdventureWorks2017] FROM  DISK = N'/var/opt/mssql/backup/AdventureWorks2017.bak' 
WITH  FILE = 1,  
MOVE N'AdventureWorks2017' TO N'/var/opt/mssql/data/AdventureWorks2017.mdf',  
MOVE N'AdventureWorks2017_log' TO N'/var/opt/mssql/log/AdventureWorks2017_log.ldf', 
NOUNLOAD,  STATS = 1 

Answer 1

Was able to workaround this problem, by creating an empty database first and then restoring with replace option.

Answer 2

I have reproduced this issue on Windows 1909, Docker Desktop 2.3.0.3, and SQL Server 2019.

I verified 3 workarounds

1. Daniel's workaround of creating the database and restoring over it
2. Create the files manually before restoring over them
3. Use volume mapped storage instead of host folder mapped storage

I found the issue only occurs when you specify the docker -v or --mount flag to map a host folder to a container folder. Unfortunately that is exactly what I wanted to do in order to take advantage of various storage volumes attached to my host.

I was able to successfully restore databases to other folders including volume mapped folders, subject to permissions. Either ensure the folder is writable by mssql user, or run the process as root.

It also works to create the files manually before restoring over them. Note you need to use the REPLACE option in restore, even though the database does not yet exist.

docker run `
    -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=$pwd" `
    -e "MSSQL_DATA_DIR=/home/data" `
    -e "MSSQL_LOG_DIR=/home/log" `
    -e "MSSQL_BACKUP_DIR=/home/backup" `
    --mount source=sql2019sysdatavol,target=/var/opt/mssql `
    --mount type=bind,source="E:\SQL2019\Data",target=/home/data `
    --mount type=bind,source="E:\SQL2019\Log",target=/home/log `
    --mount type=bind,source="E:\SQL2019\Backup",target=/home/backup `
    --name sql2019 --hostname sql2019 `
    -p 1433:1433 `
    -d mcr.microsoft.com/mssql/server:2019-latest

docker container exec sql2019 touch /home/data/AdventureWorks2019.mdf
docker container exec sql2019 touch /home/log/AdventureWorks2019_Log.ldf

$cmd = " `
    RESTORE DATABASE [AdventureWorks2019] `
    FROM  DISK = N'/home/backup/AdventureWorks2019.bak' `
    WITH  FILE = 1, STATS = 5, REPLACE, `
    MOVE N'AdventureWorks2017' TO  N'/home/data/AdventureWorks2019.mdf', `
    MOVE N'AdventureWorks2017_Log'  TO  N'/home/log/AdventureWorks2019_Log.ldf'"

sqlcmd '-Usa' "-P$pwd" '-S127.0.0.1,1433' "-Q"$cmd""

5 percent processed.
...
100 percent processed.
Processed 26344 pages for database 'AdventureWorks2019', file 'AdventureWorks2017' on file 1.
Processed 2 pages for database 'AdventureWorks2019', file 'AdventureWorks2017_log' on file 1.
RESTORE DATABASE successfully processed 26346 pages in 3.018 seconds (68.198 MB/sec).

Answer 3

I struggled with this issue for hours. Problem is the directory didn't have full permission.

chmod 777 hds
cd hds

sqlcmd -U SA -P <db_password>  -Q "RESTORE DATABASE XSP_A0 FROM DISK=N'/home/hds/DBchema.bak' WITH REPLACE, MOVE N'XSP_A0' TO N'/home/hds/XSP_A0.mdf' , MOVE N'XSP_A0_log' TO N'/home/hds/XSP_A0.ldf'"