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.
- Sql Server is running in docker. Getting Started
- A SQL Server account w/ adequate permissions
.bakfile to restore from
Restore the Database
In order to restore the restore the backup file into SQL Server on Docker we will have to take the following steps:
- Copy backup file into container
- Log into SQL Server and run
- Determine which files will be restored from the backup
- 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:
|mydb||D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mydb.mdf|
|mydb_log||D:\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
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.
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
mv commands in the container to troubleshoot the location of the
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