MS SQL Server Docker container restore database from .bak file created in Windows to a New Location

Yesterday, a client which uses Microsoft SQL Server 2008 R2 (SP2) wanted to test if his database backup can work in Microsoft SQL Server 2019 Developer version. He currently runs his SQL Server instance in Windows Server. He provided us with a .bak file, let us call it for this post WinCarFactorySQL.bak.

Run a Microsoft SQL Server Docker container

In order to test quickly, we are going to use the official, Ubuntu based, Microsoft SQL Server 2019 docker container. Therefore we download it:

docker pull mcr.microsoft.com/mssql/server:2019-CU18-ubuntu-20.04

and run it in Developer mode:

docker run -d \
    --name sqlserver2019 \
    -e "ACCEPT_EULA=Y" \
    -e 'MSSQL_SA_PASSWORD=mysuperduperpassworD1234!56' \
    -e "MSSQL_PID=Developer" \
    -p 1433:1433 \
    -v "/home/mylinux/wincarfactorybak:/opt/wincarfactorybak" \
    mcr.microsoft.com/mssql/server:2019-CU18-ubuntu-20.04

As you can see we mount /home/mylinux/wincarfactorybak directory which contains the WinCarFactorySQL.bak in order to use it inside the container in /opt/wincarfactorybak directory.

For more information you can read: Quickstart: Run SQL Server Linux container images with Docker

Examine .bak file contents

In order to restore the database properly, we have to examine the .bak file contents. This can be achieved by executing this command on the running container:

docker exec -it sqlserver2019 /opt/mssql-tools/bin/sqlcmd -S localhost \
   -U SA -P 'mysuperduperpassworD1234!56' \
   -Q 'RESTORE FILELISTONLY FROM DISK = "/opt/wincarfactorybak/WinCarFactorySQL.bak"' \
   | tr -s ' ' | cut -d ' ' -f 1-2

The output looks like this:

LogicalName       PhysicalName
-----------       ------------
WinCarFactorySQL       c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\WinCarFactorySQL.mdf
WinCarFactorySQL_log   c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\WinCarFactorySQL_log.ldf

(2 rows)

For more information you can read: Restore a SQL Server database in a Linux Docker container

Restore database

Now we have to import and relocate these two files into the directory SQL Server uses to store these files in this container. The docker container uses a non-root user called mssql. Therefore in order to find the directory we searched for *.mdf files:

bob@bobis:/home/mylinux$ docker exec -it  sqlserver2019 /bin/bash
mssql@7b208ca8f759:/$ f^C
(failed reverse-i-search)`fin': ^C
mssql@7b208ca8f759:/$ find -iname "*.mdf"
./var/opt/mssql/data/tempdb.mdf
./var/opt/mssql/data/msdbdata.mdf
./var/opt/mssql/data/model_replicatedmaster.mdf
./var/opt/mssql/data/model_msdbdata.mdf
./var/opt/mssql/data/model.mdf
./var/opt/mssql/data/master.mdf
mssql@7b208ca8f759:/$ whoami
mssql

Finally we restore the WinCarFactorySQL.bak file using the following command:

docker exec -it sqlserver2019 /opt/mssql-tools/bin/sqlcmd -S localhost \
    -U SA -P 'mysuperduperpassworD1234!56' \
    -Q 'RESTORE DATABASE WinCarFactorySQL FROM DISK = "/opt/wincarfactorybak/WinCarFactorySQL.bak" WITH MOVE "WinCarFactorySQL" TO "/var/opt/mssql/data/WinCarFactorySQL.mdf", MOVE "WinCarFactorySQL_log" TO "/var/opt/mssql/data/WinCarFactorySQL_log.ldf"'

If you get an output similar to the following, then your database restoration was successful!

Processed 130576 pages for database 'WinCarFactorySQL', file 'WinCarFactorySQL' on file 1.
Processed 1 pages for database 'WinCarFactorySQL', file 'WinCarFactorySQL_log' on file 1.
Converting database 'WinCarFactorySQL' from version 661 to the current version 904.
Database 'WinCarFactorySQL' running the upgrade step from version 661 to version 668.
Database 'WinCarFactorySQL' running the upgrade step from version 668 to version 669.
-- some lines truncated --
Database 'WinCarFactorySQL' running the upgrade step from version 901 to version 902.
Database 'WinCarFactorySQL' running the upgrade step from version 902 to version 903.
Database 'WinCarFactorySQL' running the upgrade step from version 903 to version 904.
RESTORE DATABASE successfully processed 130577 pages in 10.623 seconds (96.030 MB/sec).

For more information about these commands, you can read:

• Restore Files to a New Location (SQL Server)
• Change default database file and backup paths in SQL Server on Linux

Note 1: Regarding directory permissions

If you decide to use another directory to MOVE your .mdf and .ldf files into any other random directory, you might stumble uppon permission issues. For instance if instead of using /var/opt/mssql/data you use /opt/wincarfactorybak which is not writable by mssql user you might get errors like this:

The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on '/opt/wincarfactorybak/WinCarFactorySQL.mdf'.
Msg 3156, Level 16, State 5, Server 7b208ca8f759, Line 1
File 'WinCarFactorySQL' cannot be restored to '/opt/wincarfactorybak/WinCarFactorySQL.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Server 7b208ca8f759, Line 1
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on '/opt/wincarfactorybak/WinCarFactorySQL_log.ldf'.
Msg 3156, Level 16, State 5, Server 7b208ca8f759, Line 1
File 'WinCarFactorySQL' cannot be restored to '/opt/wincarfactorybak/WinCarFactorySQL_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Server 7b208ca8f759, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Server 7b208ca8f759, Line 1
RESTORE DATABASE is terminating abnormally.

Note 2: Regarding SQL server version where backup was made

If you received a database backup which fails to be restored locally getting the following error:

Msg 3169, Level 16, State 1, Server 203c41e3a2e1, Line 1
The database was backed up on a server running version 16.00.1000. That version is incompatible with this server, which is running version 15.00.4261. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Server 203c41e3a2e1, Line 1
RESTORE DATABASE is terminating abnormally.

then it seems that the backup has been created using a different Microsoft SQL Server version than the one that you are running in your container. In this example we took help from https://sqlserverbuilds.blogspot.com/ and we found out that:

1. The backup was created using Microsoft SQL Server 2022 with build 16.0.1000.
2. We tried to restore it in an older Microsoft SQL Server 2019 with build 15.0.4261.

Therefore we need to run a container using an updated image according to this table which will be in our case: mcr.microsoft.com/mssql/server:2022-CU9-ubuntu-20.04