How to change default backup directory in SQL Server on Linux

By: Rajendra Gupta   |   Updated: 2017-05-25   |   Comments (3)   |   Related: > SQL Server on Linux

Problem

In previous tips we have seen how to install SQL Server and administration tips for SQL Server on Linux. In this tip, I will show you how to change the default backup location on a Linux based SQL Server instance compared to a Windows based SQL Server.

Solution

Microsoft recently released SQL Server vNext to run on Linux operating systems. As part of typical best practices, a DBA often changes the default location for SQL Server backups. In SQL Server running on a Windows system, we can set this in the SQL Server installation wizard as well as changing SQL Server properties after the installation.

In SQL Server vNext on Linux, SQL Server configuration changes are done by the configuration script mssql-conf. It works on Red Hat Enterprise Linux, SUSE Linux Enterprise Server and Ubuntu. We can use this utility to set the following parameters:

• TCP port: Change the port where SQL Server will listen for connections.
• Default data directory: Change the directory for new SQL Server database data files (.mdf).
• Default log directory: Changes the directory for new SQL Server database log files (.ldf).
• Default dump directory: Change the directory where SQL Server will create memory dumps and other troubleshooting files.
• Default backup directory: Change the directory where SQL Server will save backup files by default.
• Set traceflags: Set traceflags the service will use.
• Set collation: Set a new collation for the SQL Server on Linux.

In previous tips we covered changing the default port on Linux and enabling and disabling trace flags on Linux. Now we will see how to change the default backup location for SQL Server running on Linux.

We will be using SQL Server vNext CTP1.4 for the demonstration.

In SQL Server on Linux, the default location where backup files are created is /var/opt/mssql/data. We can check this by connecting to SQL Server on Linux using SQL Server Management Studio on a Windows machine and check the backup location properties if we right click on the instance, select Properties and look at the Database Settings page.

Please note that the path displayed in the "Backup Database" looks like C:\var\opt... (instead of the native Linux path). This is a known bug in SSMS and will be fixed in a future release.

Now to change the default backup location, first we have to create the folder where we want the backup to be placed. Suppose we want the backup default location to be set to /var/sqlbackup, so we will create a directory using mkdir as follows:

# Create backup directory.
sudo mkdir /var/sqlbackup

Now we will check the folder using ls -lrt command

We can see the new folder above and we can also see that root is the owner of the folder. So in order to change the default location to work with SQL Server, we have to change the owner to mssql.

First we have to change the user permission from root to mssql. The chown command is used to change the owner and group of files, directories and links. By default, the owner of a filesystem object is the user that created it. The group is a set of users that share the same access permissions (i.e. read, write and execute) for that object.

# Syntax to change ownership of the directory.
sudo chown mssql /var/sqlbackup

Now let's verify the directory properties again.

We can see the owner of the directory is changed to mssql, but we have to change the group ownership of the directory as well. We will use the chgrp command to change the group ownership of a file/directory. All files in Linux belong to an owner and a group. The owner is set by the chown� command and the group by the chgrp command.

# Syntax to change group ownership of the directory.
sudo chgrp mssql /var/sqlbackup

Now if we look at the directory property again using ls -lrt we can see both the directory owner and group is changed to mssql.

Once the directory is created and permissions are set, we can use the mssql-conf utility to change the default backup directory. This will modify the current default backup location to the new location.

# Syntax to change default backup directory.
sudo /opt/mssql/bin/mssql-conf set defaultbackupdir /var/sqlbackup

Once we have changed the default backup directory, it will require the SQL Services to be restarted.

# Syntax to restart SQL Services on Linux
systemctl restart mssql-server.service

Now we can verify the new default location using SQL Server Management Studio on a Windows machine again, we will see the following.

Backup Databases in SQL Server on Linux

Database backups for SQL Server on Linux are similar to SQL Server on Windows (i.e. full, differential, transaction log) and the backup type depends upon the recovery model of the database.

Let's create a new database using CREATE DATABASE as follows.

Once the database is created, we can run a backup.

# Syntax to backup database SQL Services on Linux
backup database mssqltips to disk='mssqltips.bak'

Here is the output of the above command.

Once the database backup completes successfully, we can check the backup folder contents to see the backup.

# Syntax to check directory content in Linux

cd /var/sqlbackup
ls -lrt

Verify the Updated Default Backup Directory

As we have seen, the mssql-conf tool is used to change multiple configuration options in SQL Server on Linux. This tool creates a configuration file to store the configuration changes that the user has specified. These configurations are stored in a config file by the name of mssql.conf in location /var/opt/mssql.

By default, this tool does not create the mssql.conf file after installation of SQL Server on Linux, it is only created when we modify any of the configuration parameters.

Since we have modified the defaultbackupdir, if we open the mssql.conf file we can see the new backup location as shown below.

We have seen how to change the default backup directory in SQL Server on Linux, so change per your requirements.

Next Steps

• Read more about SQL Server on Linux Tips.
• Read more about SQL Server Docs on Linux.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips