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

I have the same problem with Docker SQL Server 2019 under MacOS. I assigned 777 to the whole Data directory (from MacOS). I am able to create the new database but the restore will fail anyway even with the OVERWRITE option.

What worked for me was to restore the backup in a SQL Server running on a virtualized Windows, then detach the database, copy the MDF & LDF files to the docker's data folder and attach it back on the dockerized SQL Server.

If I then backup that database from the dockerized SQL Server, I can restore it with no problem.

Answer 2

i faced the same issue on mac os. I'm using Docker desktop. So, i got such error, bc docker desktop has limitations for "virtual disk limit" setting. Open Docker Desktop -> settings -> Resources -> virtual disk limit and increase disk limit, after that change everything working fine.

Answer 3

I am running sql server on linux container, running on Windows 10 laptop. The command I used is as follows.

docker run --name testdb3 -d -p 1433:1433 -e MSSQL_SA_PASSWORD=G52ndTOur -e ACCEPT_EULA=Y -v D:/Vivek/Trials/SqlDocker/data:/var/opt/mssql/data -v D:/Vivek/Trials/SqlDocker/log:/var/opt/mssql/log -v D:/Vivek/Trials/SqlDocker/secrets:/var/opt/mssql/secrets mcr.microsoft.com/mssql/server

Once the command executes and the container is running, This will create three folders on the windows machine as follows.

Now copy your .bak file into one of those folders, say data folder.

Connect to the server running in the container using SSMS as follows. The ',1433' is optional because 1433 is default. So a dot(. representing localhost) should also work.

Next Create the database that you are trying to restore. In my case its SqlServerOnDocker

Now Right click Databases and select Restore database

Click Ok, and then ok again. Now select Options as shown below in the same Restore Database dialog.

Now click ok to restore your db.

Answer 4

I am on a mac trying to use mounted host volumes for the data , and I found that this was down to the case of the file names.

Answer 5

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 6

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

Answer 7

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

Answer 8

Check whether you have provided full permissions to the folder to save the mdf and ldf of that database.