Sharing these scripts that I always use to backup Sql Databases inside a docker container.

The Why

I personally use the backup as some sort of "save point" when doing local tests. Sometimes its much faster than prepping data, especially for repeated tests.

The Prerequisites

  1. Sqlcmd - Included in many Visual Studio workflows. Check if you have it using sqlcmd -?

The Backup Script 

sqlcmd -S localhost,2436 -U SA -P Your_password123 
BACKUP DATABASE [DatabaseName] TO DISK = N'/var/opt/mssql/backup/DatabaseName.bak' WITH INIT
GO
QUIT

docker exec -it mssql bash
ls var/opt/mssql/backup -a # This will list all files in this directory
exit

The sample to restore the database.

When you backup, you gotta restore it right?

sqlcmd -S localhost,2436 -U SA -P your_sa_password
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [DatabaseName];
RESTORE DATABASE [DatabaseName] FROM DISK = N'/var/opt/mssql/backup/DatabaseName.bak';
USE [DatabaseName];
SELECT name FROM sys.databases WHERE name = 'DatabaseName';
GO
QUIT