How to backup and restore SQL Server on Linux

by

In this article, we will describe how to create a backup of a SQL Server that is installed on Linux.

How does SQL Server create a backup?

You can create a SQL Server backup by running the T-SQL command BACKUP DATABASE.This command saves the backup file to a specified directory. SQL Server backups are created quickly because it requires just copying data from the database files to the backup file.

When creating a backup, you do not need to stop SQL Server or switch it to single-user mode, and the database continues to work as usual during this operation.

Tools

To create a SQL Server database backup, you need to execute an SQL command. There are two utilities that allow you to execute arbitrary SQL commands:

  • sqlcmd  –  is a command-line utility. To run an arbitrary query, use the -Q, parameter, like this:
sqlcmd -U sa -P my-private-password -Q 'select "Hellow word"'

Privileges

SQL Server is running on behalf of the mssql user. The mssql user must have write permissions to the backup directory. For example, let’s create a directory for backups in the root directory:

sudo mkdir -p /backups/sql-server

and assign the mssql user as the owner of this directory:

sudo chown mssql /backups/sql-server

It is also necessary for the database user to have permission to create a backup. The system administrator (sa) has this privilege by default. The process of assigning the necessary privileges to other users is described in this article.

Backup creation

To create a backup, run the BACKUP DATABASEcommand, specifying the path to the directory where you want to save your backup:

BACKUP DATABASE [test-database] TO DISK = "/backups/sql-server/full.bak";

The BACKUP DATABASEcommand has many useful options, e.g., you can enable compression, split a backup into several files, etc. You can read more about this command in the official documentation.

Restoring the same database

For SQL Server to read a backup, the mssql user must have read permissions for the directory where the backup is located. You can restore the backup with the RESTORE DATABASESQL command:

RESTORE DATABASE [test-database] FROM DISK = "/backups/sql-server/full.bak" WITH REPLACE;

Please note that the REPLACE option is used in the above example, and the existing database will be overwritten entirely without the possibility of canceling the operation.

Creating a new database from backup

To create a new database from a backup command in the restore, you must specify the name of the new database and the path to the data files that will be created after the creation of a backup.

First, you need to understand what files are in the backup. You can do this by running the RESTORE FILELISTONLYcommand:

RESTORE FILELISTONLY FROM DISK = "/backups/sql-server/full.bak"

It is better to run this command in SSMS, but if that is not possible, sqlcmd will also work. In the picture below, the logical names of the database files (test-database and test-database_log) and the physical paths to which these files will be restored by default are highlighted in red. If these files already belong to another database, the restore will fail.

In the restore command, for each file, you must map the path to a new data file (the file will be created automatically) using MOVE:

RESTORE DATABASE [test-database-2] FROM DISK = "/backups/sql-server/full.bak"
WITH 
MOVE "test-database" TO "/var/opt/mssql/data/test-database-2.mdf",
MOVE "test-database_log" TO "/var/opt/mssql/data/test-database-2_log.ldf",
RECOVERY, REPLACE

Please note that the mssql user must have access to the directory where the new data files will be located.

The RESTOREcommand has many other options. You can read more about it in the official documentation.

Differential and Transaction Log backups

SQL Server allows you to make not only complete copies of the database but also incremental. Incremental backups are those that contain only what has changed since the last backup was created. SQL Server has two types of incremental backups:

A differential backup contains all data that has changed since the last full backup was created. To make a Differential backup, specify WITH DIFFERENTIALat the end of the command:

BACKUP DATABASE [test-database] TO DISK = "/backups/sql-server/diff.bak" with Differential;

To restore the database, you will need not only a differential backup, but also a preceding full backup:

RESTORE DATABASE [test-database] FROM DISK = '/backups/sql-server/full.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE [test-database] FROM DISK = '/backups/sql-server/diff-1.bak'

Transaction log backup contains all data that has changed since any last backup, including another transaction log backup. Thus, Transaction Log backups can be combined into chains, which can be very long. But it takes the whole chain to recover it. Any chain of a transaction log backup starts with a full backup.

BACKUP DATABASE [test-database] TO DISK = '/backups/sql-server/full.bak'

Now you can create transaction log backups

BACKUP LOG [test-database] TO DISK = '/backups/sql-server/log1.bak'

and another one; you can make many such backups:

BACKUP LOG [test-db] TO DISK = '/backups/sql-server/log2.bak'

You will need all the files that were created by the last full backup. The intermediate files must be restored with the NORECOVERYoption, and the last file with the RECOVERYoption:

RESTORE DATABASE [test-database] FROM DISK = '/backups/sql-server/full.bak' WITH NORECOVERY, REPLACE 
RESTORE LOG [test-database] FROM DISK = '/backups/sql-server/log-1.bak' WITH NORECOVERY 
RESTORE LOG [test-database] FROM DISK = '/backups/sql-server/log-2.bak' WUTH RECOVERY

Transaction Logs can be created often, even every 15 minutes, and stored for a long time because they take up very little space. The disadvantage of incremental backups is that they are difficult to manage. If you make a mistake, such as losing one file, you lose the ability to recover the entire chain.

Backup automation

Backups should be created regularly and automatically. You can automate SQL Server backups in different ways. The following ways will be discussed below:

  1. Shell script and crontab
  2. SqlBak
  3. SQL Server Agent

Let’s take a closer look at them:

Shell script and crontab

Below is a script that does three simple things.

  1. Creates a compressed backup in the specified directory
  2. Removes backups that were created more than 30 days ago
  3. Sends a backup status notification to your email

You will need to install the utilities to send email notifications. You can do this with this command:

sudo apt-get install postfix mailutils

Now create a text file do-backup.sh and insert the following text. In the first lines of the script, specify the data to connect to the database:

#sqlcmd path
sqlcmdpath=/opt/mssql-tools/bin/sqlcmd
# Backup storage directory
backupfolder=/backups/sql-server/
# Notification email address
recipient_email=*****@gmail.com
# SQL Server user
user=sa
# SQL Server Password password
password=****password****
# Number of days to store the backup
keep_day=30
bakfile=$backupfolder/all-database-$(date +%d-%m-%Y_%H-%M-%S).bak

# Create a backup
$sqlcmdpath -U $user -P $password -Q "BACKUP DATABASE [test-database] TO DISK = '$bakfile.bak' WITH COMPRESSION;"

if [ $? == 0 ]; then
  find $backupfolder -mtime +$keep_day -delete
  echo 'Backup created'
else
  echo 'sqlcmd return non-zero code' | mailx -s 'No backup was created!' $recipient_email
  exit
fi

echo $bakfile | mailx -s 'Backup was successfully created' $recipient_email

You can schedule this script to run regularly with crontab. Enter:

crontab -e

and add the following line at the end:

00 00 * * * /home/user/script/backup.sh

backup creation will start at midnight.

SqlBak

SqlBak is a service for creating backups and sending them to the cloud. SqlBak can automatically make a backup of your SQL Server database, compress it, send it to any cloud storage, and notify you via email if something goes wrong. SqlBak greatly simplifies the management of incremental backups. You only need to set up regular backups once, and your backup is just a click away from restoring.

Below is a step-by-step instruction on how to backup and restore SQL Server databases via SqlBak.

Backup
  1. First of all, you need to run the SqlBak app on Linux and set a connection with sqlbak.com. The detailed instruction on how to it can be found on the “Download” page. If everything is set correctly your server will be displayed on the “Dashboard” page.
  2. Now it’s time to set a connection to your SQL Server. The connection can be added in the following way: 
    sudo sqlbak -ac -dt mssql -u sa -p **********

  3.  Once the connection is set, click on the “Add new job” button and create a new backup job.
  4. At the opened window, please choose the databases you are going to backup
  5. The next step is to set the destinations where the backups will be stored, for example, a local folder and Google Drive
  6. Now you can set a schedule when a backup job should be run
  7. And the last setting from the minimum set is the notification setting

That’s all, to run the backups according to the schedule you need to save the settings by clicking the “Save & Exit” button at the top of the page.

Restore

The backups can be easily restored by clicking the “restore” icon on your backup job’s settings page. Just choose a backup you need to restore, click the “restore” icon and follow the instructions. If it is a transaction log or differential backup, all the needed backups will be restored automatically.

Also, the very last backup can be restored from your “Dashboard” page, just click the “restore” icon at the “LAST RUN” column.

SQL Server Agent

SQL Server Agent is a separate component of SQL Server that runs in a separate process independently of SQL Server and allows you to run maintenance tasks. This component is installed together with SQL Server.

By default, the SQL Server Agent is not running. To start it, run the following commands:

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server.service

You can set up tasks in SQL Server agent by running SQL commands, but it is not very convenient. SQL Server Agent is configured through a set of stored procedures. The parameters of these procedures and the links between them are not human-friendly. Usually, these commands are executed in the backend, and the user configures what they need through SQL Server Management Studio. If you still want to configure the SQL server agent via stored procedures, see the official documentation.

SQL Server Management Studio can only be installed on Windows, but that’s okay because you can connect to SQL Server remotely through port 1433.

Find the SQL Server Agent section in Object Explorer, right-click on Jobs, and select New Job….

In the General section, enter the name of the job you want to add, for example, Daily Backup

In the Steps section, click New, and enter the following script:

declare @cutOffDate datetime = DATEADD(DAY, 30, getdate()); --The cut off date for what files need to be deleted.
DECLARE @folder NVARCHAR(512) = 'c:/backups/sql-server/'  -- path to backup directory
declare @fileExtension varchar(100) = 'bak'; --File extension.
DECLARE @backupFileName NVARCHAR(512) = @folder + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),127),':','-') + @fileExtension 

BACKUP DATABASE [test-database] TO DISK = @backupFileName
EXECUTE master.dbo.xp_delete_file 0, @folder, 'bak', @cutOffDate, 0;

It will create a backup in the directory specified at the beginning of the script and delete files that are older than 30 days. Please note that the second line has a path that starts with the windows drive label: ‘c:/backups/sql-server/’. It is correct. The path c:/ is equivalent to the root directory.


Under “New Job Schedule,” create a new schedule for daily backups.

After that, save your settings.

Now click on the job in the job list, and select Start job at Step. If everything is set up correctly, the following message will pop up:

And a new backup will appear in the catalog.

Bottom line

Backing up SQL Server on Linux is no more difficult than on Windows. The differences are in file path format and permissions.

Automated Differential and Transaction log backups can significantly increase the protection of the database by reducing the weight of backups and the speed of their creation. To avoid confusion, we suggest using the SqlBak service. It manages backups and allows you to immediately take a backup outside of the server where the SQL Server is installed, for example, to the cloud storage.

No matter how you make backups, remember the main thing: backups are created so that they can be restored. Any server can crash. So be sure to make sure your backups are working, ideally in a test environment.

1 thought on “How to backup and restore SQL Server on Linux”

Leave a Comment