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 drop 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 docker container that you want to backup.
2. Open SSMS and connect to your running container.
3. In the Object Explorer pane, open the Databases section.
4. Right click on the database you want to backup and choose Tasks > Backup.
5. Save to /var/opt/mssql/data/DatabaseName.bak on docker container 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. (i.e. 7aa8fead43af)
7. Run docker cp to copy backup file to host system.
   • docker cp 7aa8f:/var/opt/mssql/data/DatabaseName.bak /path/on/host/
8. Repeat steps 3 - 7 for each database.

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

Restore

1. Stop any running containers.
2. Remove old docker volume with docker volume rm volumename.
   • DANGER: you cannot undo this. Do not continue until you have backed up all your data to your host system.
3. Set permissions on .bak files to allow all.
   • sudo chmod 777 DatabaseName.bak
4. Start new docker container.
5. Find the running docker container id with docker ps. Make note of the first 5 characters.
6. Copy all .bak files to new docker volume
   • You need to do this one at a time, wildcards will not work.
   • The -a flag is important, it preserves the file permissions we added in the first 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 choose Restore Database.
9. Under the Source section, choose Device and click the kebab menu button.
10. Click Add and choose one .bak file under /var/opt/mssql/data/
11. The Restore Plan menu should appear and be populated. The default settings are fine.
12. Click OK to start the Restore process.
13. Repeat steps 6 - 11 until you have restored all your databases.
14. Remove the old .bak files from your new 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 in the middle of this process, you can delete the .bak files after each restore instead of waiting until the last step.