How to Backup SQL Server on Linux

You can back up your SQL Server databases on Linux using at least one of the following approaches:

• by using SqlBak that will do everything for you
• by running the sqlcmd client directly on your Linux machine (this will be described in this post below)
• by running a custom python script

This post describes how to backup SQL Server on Linux manually via a command line. To begin with, make sure that you have successfully installed mssql-tools, the package that includes sqlcmd. Then start your sqlcmd client as follows:

sqlcmd -H localhost -U SA

This connects to the system administrator account on the databases that are local to your server. You will then be prompted for a password (if you don’t recall your password, note that you may have set it up when you installed SQL Server initially, specifically when you ran mssql-conf setup).

Next, at the sqlcmd client prompt, enter the following on successive lines:

BACKUP DATABASE [yourdbname] TO DISK = ‘yourdbname.bak’
GO

This will perform a full backup of the database that you specify (“yourdbname”) to /var/opt/mssql/data, and will give it the name “yourdbname.bak.” You will see the progress of the backup, and you can, of course, change the target location (it could be to the Azure cloud, for example, by substituting the command TO URL for TO DISK). There are also multiple options that you can specify. For example,

BACKUP DATABASE [yourdbname] 
       TO DISK = ‘yourdbname.bak’,
          DISK = ‘yourdbname.bak2’,
          DISK = ‘yourdbname.bak3’
          WITH STATS = 10
GO

will divide your backup into three files of roughly the same size, and will log to the terminal each time 10% of the backup is successfully completed. Note that sub-statements of the main clauses (the main clauses here are TO and WITH) generally require termination by commas, whereas the main clauses themselves do not. You can explore additional backup options, such as compression, media set parameters, and log-related choices, by having a look at the Microsoft documentation on the topic.

Another common need for database administrators is to perform a differential backup, rather than the default full backup (a differential backup only includes data that has been added since the last full backup). To run a differential backup, you can use the following command:

BACKUP DATABASE [yourdbname] 
       TO DISK = ‘yourdbname.bak’
       WITH DIFFERENTIAL
GO

 After performing the differential backup, you should check your file sizes with

ls  -lah

in order to see whether they make sense as partial data (however, needless to say, a differential backup could be larger than a full backup if a lot of data was added since the last full backup).

Finally, note that it is possible to start up the sqlcmd client and execute your backup commands all in one statement by using the “Q” option with sqlcmd as follows:

sqlcmd -H localhost -U SA -Q "BACKUP DATABASE [yourdbname] TO DISK = 'yourdbname.bak' WITH STATS = 10"

That’s it.