Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to change default backup directory in SQL Server on Linux


By:   |   Updated: 2017-05-25   |   Comments (3)   |   Related: More > 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.

SQL Server version

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.

SQL Server backup default location

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

SQL Server backup directory create

Now we will check the folder using ls -lrt command

SQL Server backup directory permissions

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.

SQL Server backup directory permission

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.

SQL Server backup directory permission for the owner and group

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

Change the SQL Server backup directory

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.

SQL Server backup directory verification in Management Studio

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.

SQL Server databases on the instance

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.

SQL Server database backup for the mssqltips database

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

SQL Server database backup verification
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.

SQL Server default database backup directory

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

Next Steps


Last Updated: 2017-05-25


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, November 30, 2017 - 11:07:40 PM - rajendra gupta Back To Top

Hi, In recent release syntax to change default backup directory has been changed.

Old Syntax

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

New syntax

sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /tmp/backup

 

Please use the new code and you will be able to change default backup directory.

 

Please let me know any questions.

 

Thanks

Rajendra


Thursday, November 30, 2017 - 5:10:24 PM - Jorge Antonio Back To Top
The procedure does not work because it displays the error. The ' Defaultbackupdir ' setting is not supported.

Thursday, October 19, 2017 - 5:41:23 AM - Waldy Back To Top

 The setting 'defaultbackupdir' is not supported.

 

 


Learn more about SQL Server tools