I needed to restore a database backup file to a locally running SQL Server running in a Docker container. I was using a Mac and had no desire to install SSMS in parallels.

Prerequisites

  • Sql Server is running in docker. Getting Started
  • A SQL Server account w/ adequate permissions
  • A .bak file to restore from

Restore the Database

Overview

In order to restore the restore the backup file into SQL Server on Docker we will have to take the following steps:

  1. Copy backup file into container
  2. Log into SQL Server and run sqlcmd
  3. Determine which files will be restored from the backup
  4. Run the restore command

Copy file into container

We can use the docker cp command to copy a file into the container. We will copy our file into SQL Server’s data directory

docker cp <mydb.bak> <sql-server>:/var/opt/mssql/data/mydb.bak

# where:
# <sql-server> is the name of the container
# <mydb.bak> is the name of the backup file

Determine files to be restored

Run the following command to see what files the backup file includes:

RESTORE FILELISTONLY FROM DISK = N'mydb.bak';

This command will output a list of files that we will need to account for when running the restore command. Here is an example output:

LogicalNamePhysicalName
mydbD:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mydb.mdf
mydb_logD:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mydb_log.mdf

We will need the LogicalName and the filename in the PhysicalName columns for the restore command.

Run restore command

When we run the restore database command, we have to indicate where the different logical files will live on the filesystem. Since this is running in a Docker container, it lives in a Linux filesystem and the files will have to use Linux paths.

You should be able to see in the following example where the Logical names are used with the MOVE command and the Physical file names are used in the TO portion.

Update the query to reflect your backup file name and the information gathered from the previous step.

RESTORE DATABASE mydb FROM DISK = N'mydb.bak' WITH 
MOVE 'mydb' TO '/var/opt/mssql/data/<mydb.mdf>',
MOVE 'mydb_log' TO '/var/opt/mssql/data/<mydb_log.ldf>';

Run the command and you should be good to go.

Gotchas

RESTORE DATABASE is terminating abnormally

If you run the command and get an error like this:

1> restore database mydb from disk=N'mydb.bak' with replace;
2> go
Msg 5133, Level 16, State 1, Server d9d263f75eb0, Line 1
Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mydb.mdf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Server d9d263f75eb0, Line 1
File 'mydb' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mydb.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Server d9d263f75eb0, Line 1
Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mydb_log.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Server d9d263f75eb0, Line 1
File 'mydb_log' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mydb_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Server d9d263f75eb0, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Server d9d263f75eb0, Line 1
RESTORE DATABASE is terminating abnormally.

This error is caused when you are not indicating where your files should be moved to. Make sure that you are using the MOVE TO command for all files in the backup file.

No such file or directory

This error has to do with the location of your backup file on the Docker filesystem. The directory it needs to be in is /var/opt/mssql/data/. You can use the ls and mv commands in the container to troubleshoot the location of the .bak file.

docker exec sql-server ls /var/opt/mssql/data/

mydb.bak      <<< Backup file should be in this directory
master.mdf
mastlog.ldf
model.mdf
modellog.ldf
msdbdata.mdf
msdblog.ldf
tempdb.mdf
templog.ldf