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.
Let's start with spinning up a SQL Server container called
> 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.
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.
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
- Enter the directory that matches your container, e.g.
- Enter the
- Create a
backupdirectory if it doesn't exist.
- Copy the backup file (e.g.
DATABASE.bak) to the
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/
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!
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-studioor
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
localhostas 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
sausername and the password that was supplied in the
docker runcommand 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
- Click the Restore button at the bottom of the window.
- 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'"
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
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!