Restoring an SQL server backup in a Docker Container on Windows
Tags: docker, sql
Published: 2022-02-21 11:23:40

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](https://docs.microsoft.com/en-us/windows/wsl/install) 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.

1. Use File Explorer to browse to `\\wsl$\docker-desktop-data\version-pack-data\community\docker\volumes`
1. Enter the directory that matches your container, e.g. `mssql-server-data`.
1. Enter the `_data` directoy.
1. Create a `backup` directory if it doesn't exist.
1. 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.

1. 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`)
1. Start Azure Data Studio ![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x77xtjnmjnyfun3axky3.png)
1. Connect to the database in the container
  - Expand the **SERVERS** node and click on the **New Connection** icon ![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7sbtqfmc08txbnn9obfa.png)
  - 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 ![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j84iyjfbl5mbngcpcumy.png)
  - Select the new connection in the connections tree view and click the **Restore** button ![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/auepkehgc5n59q0242lp.png)
  - 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

1. 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'"
  ```

1. You should see output similar to the following:

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

1. 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).

1. Click the __New connection__ button.
1. 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>