Restoring a database on ms sql server for linux (docker)

2019 May 22 at 08:14 » Tagged as :sql-server, docker, northwind, linux, swap,

Right-O Jeeves, I downloaded the Northwind backup for sql server, but how do I restore it with sqlcmd? The butler answered:

RESTORE DATABASE Northwind FROM DISK='/tmp/Northwind.bak'

But that produced

Msg 3201, Level 16, State 2, Server 61ea3d23c7fb, Line 1
Cannot open backup device '/tmp/Northwind.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server 61ea3d23c7fb, Line 1
RESTORE DATABASE is terminating abnormally.

Jeeves isn't in top form these days. Either that or he is still smarting over that incident of the purple socks. He left me scratching around and I figured out for myself that you need to move the file to the location where sql server's data is peristed. In my case, I started the docker container as :

docker run -d -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=*****'   -p 1433:1433 -v /var/opt/mssql:/var/opt/mssql -d microsoft/mssql-server-linux

This means that the Northwind.bak file has to be copied to '/var/opt/mssql', and then

Directory lookup for the file "D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND.MDF" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Server 61ea3d23c7fb, Line 1
File 'Northwind' cannot be restored to 'D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND.MDF'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Server 61ea3d23c7fb, Line 1
Directory lookup for the file "D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND_log.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Server 61ea3d23c7fb, Line 1
File 'Northwind_log' cannot be restored to 'D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Server 61ea3d23c7fb, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Server 61ea3d23c7fb, Line 1
RESTORE DATABASE is terminating abnormally.

This is paydirt! According to MSDN, you need to make a close note of these paths, and change your restore command in sqlcmd appropriatelty. Something like:

RESTORE DATABASE Northwind FROM DISK='/var/opt/mssql/Northwind.bak' WITH MOVE 'Northwind' TO 'D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND.MDF', MOVE 'Northwind_log' TO 'D:\MSSQLDataFiles\UNTC\DB_All\DB\NORTHWND.LDF'

What, still the same error!! Then I found a stackoverflow answer that lead me down the right track. You have to use the actual file system paths on linux rather than the virtual paths that are shown in the error.

RESTORE DATABASE Northwind FROM DISK='/var/opt/mssql/Northwind.bak' WITH MOVE 'Northwind' TO '/var/opt/mssql/data/NORTHWND.MDF', MOVE 'Northwind_log' TO '/var/opt/mssql/data/NORTHWND_log.ldf' Go

What-ho! What-ho! What-ho!