This simple guide will help you create backups of SQL Server on Linux.

SQL Server backups on Windows and Linux: what’s the difference?

The only noticeable difference is in the graphical tools.

SSMS is the main tool for SQL Server and is only available on Windows. Connecting from Windows to SQL Server on Linux is not always possible due to port security (1433). You will have to use the command line utility sqlcmd and T-SQL commands.

Step-by-step instructions for the first backup

1. Create a backup directory:

sudo mkdir -p /var/opt/mssql/backups

2. Grant SQL Server permissions so it can create backups in the directory:

sudo chown mssql:mssql /var/opt/mssql/backups
sudo chmod 755 /var/opt/mssql/backups

3. Enable sqlcmd interactive mode:

/opt/mssql-tools/bin/sqlcmd -U sa -P sa_password

4. Perform a backup:

BACKUP DATABASE [YourDatabaseName] 
TO DISK =  '/var/opt/mssql/backups/YourDatabaseName.bak'

Automation with Cron

crontab -e

Add two lines to the bash script: the first will create a new backup every 24 hours, the second will delete backups older than 30 days:

0 0 * * * /opt/mssql-tools/bin/sqlcmd -U sa -P sa_password -Q "BACKUP DATABASE [YourDatabaseName] TO DISK = '/var/opt/mssql/backups/YourDatabaseName_$(date +\%Y\%m\%d).bak'"
0 0 * * * find /var/opt/mssql/backups -name '*.bak' -mtime +30 -exec rm {} \;

Restore an SQL Server database on Linux

The restore command is very simple and is also executed via sqlcmd. The main thing is to ensure that SQL Server has access to the backup file:

RESTORE DATABASE [YourDatabaseName] 
FROM DISK = '/var/opt/mssql/backups/YourDatabaseName.bak 
WITH REPLACE;

SqlBak

Creating a bash script is not the only way to automate backups. An excellent alternative to bash script is a cloud backup service: SqlBak.

SqlBak creates a backup, compresses it, encrypts it, transfers it to the selected cloud storage, deletes old backups so that the storage does not overflow and then sends an email notification about the results of its work.

The service is quite easy to set up, but its main advantages are revealed when it comes to managing backup automation across several servers at once.

Follow this link to install SqlBak app on Linux.

Download package:

curl -o sqlbak.deb https://sqlbak.com/download/linux/latest/sqlbak.deb

Install the app:

sudo apt-get install ./sqlbak.deb

Register a server:

sudo sqlbak -r -k <SECRET KEY>

After successful registration, your server will appear on the Dashboard page.

To create a new job to back up your databases, go to the Dashboard page and click the Add New Job button. Then, check the settings and click Create Job. After that, the job setup page will appear where the first two steps will be automatically configured.

Go down to the next sections and set up options like select databases, store backup destination, backup schedule, encryption, compression, etc.

How to create an SQL dump for SQL Server

For SQL Server databases, you can export to a .sql script, similar to what mysqldump does for MySQL. For this, there is a utility called mssql-scripter which can be installed via pip:

pip3 install mssql-scripter

Now in the terminal is available the command mssql-scripter. This is how you can create a dump of the SQL Server database:

mssql-scripter -S localhost -d YourDatabaseName -U sa -P sa_password --schema-and-data > dbms_dump.sql

docker run --rm --network host pranasnet/mssql-scripter -S localhost -d model -U sa -P sa_password --schema-and-data > dbms_dump.sql

SQL Server Backup in Docker

If SQL Server is installed in Docker, to ensure that the backup is saved directly to the host system, a backup directory must be bind-mounted between the SQL Server container and the host system.

For example, like this:

sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=pass" -p 1433:1433 --name sql1 --hostname sql1 -v $(pwd):/var/opt/mssql/backups -d mcr.microsoft.com/mssql/server:2022-latest

After that, you can create a backup:

sudo docker exec sql1 /opt/mssql-tools/bin/sqlcmd -U sa -P sa_password -Q "BACKUP DATABASE TestDB TO DISK = '/var/opt/mssql/backups/TestDB.bak';"

However, if you forgot to mount, you can always create a backup in the container and then simply copy it to the host system:

sudo docker cp sql1:/var/opt/mssql/backups/TestDB.bak ./

Just don’t forget to delete the backup in the container:

sudo docker exec sql1 rm /var/opt/mssql/backups/TestDB.bak

SQL Server and data file snapshots

Starting with the 2022 version, SQL Server introduced the ability to prepare database files for copying. This allows creating snapshots of SQL Server files (using LVM or ZFS) in a consistent state from which they can be normally restored as a separate database.

The following command will wait for all write operations to complete and freeze the database; however, read operations will still be available:

ALTER SERVER CONFIGURATION SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

After the server is frozen, you need to back up the server metadata, which is also done instantly:

BACKUP SERVER
TO DISK = '/bkp/server.bkm'
WITH METADATA_ONLY, FORMAT;

Now you can copy the database files or take a snapshot of the disk where the database is located.

cp /var/opt/mssql/data/YourDatabaseName.mdf /backup/YourDatabaseName.mdf
cp /var/opt/mssql/data/YourDatabaseName_log.ldf /backup/YourDatabaseName_log_1.ldf

After that, you need to return the database to normal mode:

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF;

Database restoration from file snapshots

Copy the data files from the backup to the desired location, for example, to the /var/opt/mssql/data/ directory, and set the file owner to the mssql user.

To restore the database, you need to specify the path to the data files and the path to the server metadata backup. If you are restoring the database under a different name, you also need to specify the DBNAME parameter:

RESTORE DATABASE AdwentureWorks2 
FROM DISK = '/bkp/server.bkm' 
WITH METADATA_ONLY, REPLACE, DBNAME='AdwentureWorks', 
    MOVE 'YourDatabaseName' TO '/var/opt/mssql/data/YourDatabaseName.mdf', 
    MOVE 'YourDatabaseName_log' TO '/var/opt/mssql/data/YourDatabaseName_log.ldf';

Useful resources

SqlBak — simple tool for backups

Microsoft documentation on the BACKUP DATABASE command

Microsoft documentation for creating data file snapshots

GitHub repository for the mssql-scripter utility