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

4 minute read

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:

[email protected]:~/Documents/workspace-sts-3.8.4.RELEASE/manios-blog(master)$ docker exec -it  sqlserver2019 /bin/bash
[email protected]:/$ f^C
(failed reverse-i-search)`fin': ^C
[email protected]:/$ 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
[email protected]:/$ 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:

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

Comments