How to Migrate SQL Server Databases Using Docker

Running a SQL Server database with Docker is a convenient way to manage a local database. Upgrading versions of a SQL Server docker container doesn't mean you need to lose all your data. I was recently tasked with guiding my team in safely upgrading a version of SQL Server running locally in a docker container while preserving their data. I managed to figure it out by connecting to the SQL Server instance using SQL Server Management Studio to generate backup files inside the container. Then, after copying the backup files to the host machine, I started a new container and restored the backups on the new version of SQL Server. There's probably a more elegant way to accomplish this, but I'm not willing to learn more about SQL Server to find out.

Prerequisites

• SQL Server database in a Docker container with some data you don't want to lose
• Another SQL Server database in another Docker container
• SQL Server Management Studio (SSMS)
• Enough hard drive space to store three copies, at most, of your data

Backup

1. Start the Docker container you want to backup.
2. Open SSMS and connect to the running container.
3. In the Object Explorer pane, open the Databases section.
4. Right-click on the database you want to backup and select Tasks > Backup.
5. Save the backup to /var/opt/mssql/data/DatabaseName.bak on the Docker container's volume.
   • Using the default settings is fine.
6. Find the running Docker container ID with docker ps.
   • Make a note of the first 5 or 6 characters (e.g., 7aa8fead43af).
7. Run docker cp to copy the backup file to the host system.
   • docker cp 7aa8f:/var/opt/mssql/data/DatabaseName.bak /path/on/host/

Repeat steps 4-7 for each database.

You should now have a collection of .bak files containing backups of all your data! We'll use these to restore your data to a new SQL Server instance.

Restore

1. Stop any running SQL Server containers.
2. Remove the old Docker volume with docker volume rm volumename.
   • DANGER: You cannot undo this. Do not proceed until you have backed up all your data to your host system.
3. Set permissions on the .bak files to allow all users access.
   • sudo chmod 777 DatabaseName.bak
4. Start the new Docker container.
5. Find the running Docker container ID with docker ps. Note the first 5-6 characters.
6. Copy each .bak file to the new Docker volume.
   • You must do this one at a time; wildcards will not work.
   • The -a flag preserves the file permissions we set in the previous step.
   • docker cp -a /path/to/DatabaseName.bak 3ade8:/var/opt/mssql/data/
7. Open SSMS and connect to your new SQL Server instance.
8. Right-click on Databases and select Restore Database....
9. Under the Source section, select Device and click the ellipsis button.
10. Click Add and choose one .bak file under /var/opt/mssql/data/.
11. The Restore Plan menu should now appear. The default settings are fine.
12. Click OK to start the restore process.

Repeat steps 6-12 for each database file.

1. Remove the old .bak files from your new container's volume to save space.
   • docker exec -it container-name bash
   • rm -rf /var/opt/mssql/data/DatabaseName.bak

You should now have a new Docker container with the same data that existed in your old Docker container! If you run out of disk space, you can delete the .bak files after each restore instead of waiting until the last step.