Restoring an SQL server backup in a Docker Container on Windows

Sandor Drieënhuizen — Mon, 21 Feb 2022 11:23:40 +0000

I don't know about you but personally I'm not too fond of installing SQL server directly on my development machine. Since I only occasionally need to work with an SQL Server database, it makes more sense to me to run it in a way that doesn't permanently 'pollute' my system :)

This is why, in this post, we'll be running SQL Server in a container running SQL server. We'll also restore a database backup file and finally browse through the database.

I'm assuming that you:

Are running Windows 10 or 11 with WSL2 (follow these instructions if you don't have WSL2 installed yet).
Are running Docker Desktop with WSL2 enabled in its settings.
Have an existing SQL server database backup file at hand.

Spin up a container running SQL Server

Let's start with spinning up a SQL Server container called mssql-server.



> docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" --name "mssql-server" -p 1433:1433 -v sql1data:/var/opt/mssql -d mcr.microsoft.com/mssql/server

Note that you can change the port by altering the -p 1433:1433 argument to use a different port, e.g. -p 12345:1433. The port on the right side of the colon is the internal port, which should remain unchanged.

Copy a backup file into the container

Now let's restore a previously made SQL server database backup file.

There are two ways we can go here: either do it directly through your PC's filesystem or use docker cp to copy the file to the Docker volume.

Option 1 - Copy using File Explorer

Note that the paths that are mentioned below might change in the future as the Docker team sees fit.

Use File Explorer to browse to \\wsl$\docker-desktop-data\version-pack-data\community\docker\volumes
Enter the directory that matches your container, e.g. mssql-server-data.
Enter the _data directoy.
Create a backup directory if it doesn't exist.
Copy the backup file (e.g. DATABASE.bak) to the backup directory.

Option 2 - Copy using Docker CLI

Make sure that the backup file (e.g. DATABASE.bak) is located in the current directory. Alternatively, you can specify its actual path in the docker cp statement below.



> docker exec -it mssql-server mkdir /var/opt/mssql/backup
> docker cp DATABASE.bak mssql-server:/var/opt/mssql/backup/

Restore a backup file

Now that the backup file is located in the the volume, the database server has access to it. Let's restore the backup into an actual database!

Option 1: Restore using Azure Data Studio

Note that the directories mentioned below may change in the future (or already has) as the Docker team sees fit.

Download from https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio (or choco install azure-data-studio or winget install Microsoft.AzureDataStudio)
Start Azure Data Studio
Connect to the database in the container
- Expand the SERVERS node and click on the New Connection icon
- Use localhost as the host. If you mapped the container to a port that is different from the default port 1433 you must use localhost,# instead, replacing the # with the actual port number.
- Provide the sa username and the password that was supplied in the docker run command above
- Select the new connection in the connections tree view and click the Restore button
- At Restore from, select the From File option.
- At Backup file path, click the browse button (three dots) and browse to the correct backup file in the /var/opt/ mssql/backup directory.
- Click the Restore button at the bottom of the window.

Option 2: Restore using command line

Let's find out the logical file names and paths inside the backup.



  docker exec -it mssql-server /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<YourStrong!Passw0rd>" -Q "RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/DATABASE.bak'"

You should see output similar to the following:



  LogicalName           PhysicalName
  ----------------------------------------------------------------
  Database1_Data          C:\MSSQL\Data\Database1.mdf
  Database1_Log           C:\MSSQL\Data\Database1.ldf

Finally, let's restore the backup using the listed logic file names.



docker exec -it mssql-server /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<YourStrong!Passw0rd>" -Q "RESTORE DATABASE Database1 FROM DISK = '/var/opt/mssql/backup/DATABASE.bak' WITH MOVE 'Database1_Data' TO '/var/opt/mssql/data/Database1.mdf', MOVE 'Database1_Log' TO '/var/opt/mssql/data/Database1.ldf'"

Connect to the database server

Now that our backup is restored, let's connect to the database server using Azure Data Studio (of course you can use your preferred database manager instead).

Click the New connection button.
Enter the following information:
- Connection type: Microsoft SQL Server
- Server: localhost (if you mapped the container to a port that is different from the default port 1433 you must append it to the server name, separated by a comma, e.g. localhost,12345) 1. Click the Connect button.

Finally, expand the Databases folder in the tree, expand the Tables folder and then and browse through the tables to see what's inside your database!

References:

https://docs.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container?view=sql-server-ver15

DEV Community: Sandor Drieënhuizen