In a previous article, we explored the process to change default SQL dump file location in SQL Server. In this article, we will view the process to change the default database files and backup locations in SQL Server on Linux.
While installing SQL Server on Windows, the SQL Server installer gives you the option to configure the data file, log file and the backup directories. We can set different directories for the Data root directory, user database, database log files and backup directory during the installation itself.
If we opted for the default installation of SQL Server, we could still change the default location using SSMS; connect to the database instance and go to server properties and in the ‘Database setting’ page, and modify the locations.
In SQL Server on Linux, we cannot change the default file locations with this method. During installation, we do not get an option to configure the SQL Server database directories. SQL Server on Linux does not have GUI based configuration during the installation; instead it is command based. If we connect to SQL Server on Linux using SSMS, we can see the ‘database default file locations’. However, we cannot change the default location here using SSMS. It needs to be changed using the Linux configuration utility of SQL Server, i.e. mssql-conf.
In the above screenshot, you can see the default file location is ‘/var/opt/mssql/data‘.
Let us go to the default path (/var/opt/mssql/data). Here we can view the content using the ‘ls -lrt‘ command. You can find all database file in this path, i.e. data files, log files and the backup files.
Once we create any database, it goes to this default location only. We do not need to specify the locations of the database file so that it will create all the files in that folder.
Now we want to change this default path for both the data files and the log files. Therefore, we will create a new directory with mkdir command in the desired location having sufficient free space. Run the command with the administrative permission. In below command, we are creating a directory ‘/DefaultDBPath‘.
$ sudo su
#mkdir /DefaultDBPath
We can go to this directory and can see there are no files in it currently.
If we look at the group owner and the directory permissions, currently it is set to root.
We need to change the group and the owner of this directory to mssql. Without this, SQL Server cannot access this path.
sudo chown mssql /DefaultDBPath
sudo chgrp mssql /DefaultDBPath
In the below screenshot, you can see the group and owner for / DefaultDBPath is set to mssql.
Now, we are ready to change the default file location to this directory. We will change the default file locations using the mssql-conf utility. We can find the mssql-conf utility under the /opt/mssql/bin.
We can get the list of configuration available under mssql-conf utility, run the below command.
# ./mssql-conf list
In the above screenshot, you can see the various configuration options available with mssql-conf utility. In this article, we will focus just on the modifying default locations for the database files and the backup files.
Below are the configurations available to modify the default file locations.
Configuration option | Description |
Filelocation.defaultdatadir | Modify the default directory for the data file |
Filelocation.defaultlogdir | Modify default directory for the log file |
- Modify default data file path: Run the below command to change default data file path.
/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /DefaultDBPath
- Modify default log file path: Run the below command to change default log file path.
/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /DefaultDBPath
These configurations will be enabled once we restart the SQL Server services in Linux. Run the command below.
Systemctl restart mssql-server.service
We do not get any success or failure message here. Check the service status
Systemctl status mssql-server.service
Now, connect to SQL Server using SSMS again and verify the default file locations.
We can verify this setting by creating a new database also. Create a new database and do not specify any data, log file locations. You can use SSMS create database wizard or just run the below command in the new query editor.
|
1 |
Create Database DemoSQLShack |
You can see that data and log files default locations is set to ‘/DefaultDBPath’.
Modify Default Backup location in SQL Server on Linux
In the previous step, we modified the default data and log file locations. Database administrators may need to change the default path of the backup drive as well. It is a good idea to change it as well because if someone initiates the backup for a big database, it may fill up the entire drive as well. If the default backup drive is same as of data or log file location, it could lead to a big issue for database availability.
In this example, we will use the same directory we created in the above example. You may create a new directory in the desired location. We should consider the space requirement of the disk before specifying the directory.
We need to use the below parameter in mssql-conf utility to change the default backup location.
Configuration option | Description |
Filelocation.defaultbackupdir | Modify default directory for the backup file |
Now let us change the default backup directory using the below command.
# sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /DefaultDBPath
Restart the SQL Server service to apply this setting. We can copy the command from the terminal window as well.
Once SQL Service is up again, connect the instance using SSMS and right click on it to execute a backup. You can see in below screenshot that it automatically shows the backup location in the default directory.
The best part of specifying the default backup location is that you do not need to specify the backup location in the script while taking the backup. We can just specify the backup file name, and it will place the file at the default directory. For example, in the below screenshot, you can see we did not specify any backup location.
We can go to the directory ‘/DefaultDBPath‘ and view the content of the files using the ls-lrt command.
You can see here that this path contains the database files (DemoSQLShack.mdf and DemoSQLShack_log.ldf) and the backup file (DemoSQLShack.bak). We can verify the default location in SSMS. The following screenshot shows that we have successfully changed the default file locations in SQL Server on Linux.
Conclusion:
In this article, we explored the process to change the default database locations. You should always follow this practice in your environment. If you decide to do so, you can follow this article and modify, verify the default path with simple steps.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023