Restore database in docker container

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

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

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

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