Moving Docker SQL Server databases between machines

I'm in the middle of migrating all my apps and databases to a new Macbook Air machine. Since I do all my .Net Core development on MacOS I use Docker containers to manage any SQL Server databases that I'm working with. In this blog post I'll show how I migrated my SQL Server databases between machines and Docker containers.

Create a database backup on machine 1

I used Azure Data Studio to manage SQL Server databases from my Mac. Azure Data studio is a fine substitute for SQL Server Management Studio, but it lakes the majority of the features. Though I'll take the lack of features for the ability to development on my macOS machine any day.

To back up a database from Azure Data Studio, connect to your server, navigate to the database and right click the database. Then select the Backup option from the drop down menu. Select the options you need and click Backup. Take not of the Backup files location as you're going to need this to copy the file from the Docker container to your local machine. Here my backup copy will be made in /var/opt/mssql/data folder within the current Docker container.

Copy the database backup to a central location

Now using the Docker command docker cp copy the the database backup file from the Docker container to your local machine and then to a central location where you can access the backup file from the destination machine.

1. Open the Docker dashboard on the source machine, locate the SQL Server container, then copy the container id.

2.  Open Terminal and execute the docker cp command with the following parameters: {yourContainerId}:{containerBackupFileLocation} {destinationFolder}

docker cp cbd5323d5caf78c9b7434ff46eccbba0b5c20e2e4807d71fd83a6394372fc1c0:/var/opt/mssql/data/Machine-1-2023818-11-11-11.bak ~/code

3.  This will copy the .bak file from the previous step into a folder called code on your local machine. Make sure the destination folder code exists before you run this command.

4. Transfer the .bak to a central location that the new machine can access. Something like Dropbox or shared filed location is good. And then download the file to the new machine.

5. Run the following command from the new machine to create the backup folder within the new Docker Container. sql is the name of the new SQL Server container running on the new machine.

sudo docker exec -it sql mkdir /var/opt/mssql/backup

6. Run the following command on the new machine to copy the database from your new machine into the new SQL Server container. Replacing `Machine 1-2023818-11-11-11.bak` with the name of your backup file.

sudo docker cp Machine-1-2023818-11-11-11.bak sql:/var/opt/mssql/backup

7. Open Azure Data Studio, right click on your database name and select restore. The current version of Azure Data Studio requires the database to exist before you can do a restore. If the database does not already exist, create a blank database to restore to. Navigate to the location inside the container that was created in the previous step.

That should do it. You are now able to use the old database. I actually ran into an issue during this process where the containers I was using for my new M1 machine were too far ahead of the existing database that I was trying to transfer over. So keep in mind that depending on the container you are using, certain versions of SQL Server might not be compatible between Docker instances.