How to Move SQL Server System Databases to a different directory on Linux
By: Rajendra Gupta | Updated: 2021-12-23 | Comments | Related: More > SQL Server on Linux
Do you know how to move system databases to different drives in SQL Server on Linux? If not, this tutorial goes through the steps on how to move the system databases – master, model, msdb, and tempdb to a different directory for SQL Server running on Linux.
SQL Server on Linux is a fully supported edition of SQL Server – the market-leading relational database – running on the Linux operating system. It’s a combination that delivers improved agility and business value for enterprise customers looking to build modern applications. It is a fully supported, production-ready offering from Microsoft that brings the power of SQL Server to Linux applications and enables you to use advanced analytics, machine learning, and business intelligence tools.
SQL Server on Linux works on Ubuntu, RedHat, and macOS. You can use any of these for this article.
You can use both SQL Server 2017 and 2019 versions for SQL Server on Linux. The installation process configures SQL Server with the default configurations. For example, by default, it creates the system databases – master, model, msdb and tempdb in the default path /var/opt/mssql/data. This default path is used as a storage directory for any new database.
As shown below, both the primary data file (MDF) and the transaction log file (LDF) are stored in the /var/opt/mssql/data directory. The mssql user has the file and group ownership for both MDF and LDF files.
Check SQL Server Version and Status
The steps for moving system databases to a different directory are different from SQL Server on Windows. For this tip, we are using Microsoft SQL Server 2019 CU12 – 15.0.4153.1, as shown below.
Make sure the SQL Service is active (running). You can validate the status using this command.
systemctl status mssql-server
Steps to Move SQL Server master database files to new location on Linux
Below are the steps to follow to move the master database files.
- Create new directory
- Assign permissions
- Change location of master files
- Stop SQL Server
- Move database files
- Start SQL Server
- Validate Move
1 - Create a new directory
For this article, I am creating a new directory in the same mount point. You can add a new mount point in Linux as well.
2 - Assign permissions to the new directory
Use the chown and chmod commands to change the owner and group of the directory to the mssql user.
sudo chown mssql /sqlsystemdb/ sudo chgrp mssql /sqlsystemdb/
3 - Change the default master database location using mssql-conf
The mssql-conf utility is a configuration tool for SQL Server on Linux. It is installed automatically with SQL Server installation on a Linux environment. The mssql-conf combines the functionality of sp_configure and SQL Server Configuration Manager.
The mssql-conf is a configuration file and is required to do the following configurations.
- It sets up the SA password, accepts license agreement during the initial SQL Server on Linux configuration.
- It can be used to configure (set/unset) the SQL Server configuration values
- Server collation
- SQL Agent settings
- Database mail profile
- Default data directory
- Master database directory
- TCP port
- Set local audit directory
- Enable or disable trace flags
- Set the SA password
- Log directory
- Default dump directory
- Default backup directory
- High Availability settings
- SQL Server memory limit
- Change the name of master database files
- Specify TLS settings
- Change the SQL Server locale
- Configure MSDTC
To understand more about mssql-conf utility, refer to the article.
To move the master database, we need to specify a new directory using the following parameters.
- filelocation.masterdatafile: this sets the master database primary data file (MDF) location.
- filelocation.masterlogfile: this sets the transaction log file (LDF) location for the master database.
Run the following scripts to change master database data and log file directory to /sqlsystemdb
sudo /opt/mssql/bin/mssql-conf set filelocation.masterdatafile /sqlsystemdb/master.mdf
sudo /opt/mssql/bin/mssql-conf set filelocation.masterlogfile /sqlsystemdb/mastlog.ldf
It instructs you to restart SQL Server services to apply the change in the master
database directory. Don’t use the command shown –
systemctl restart mssql-server.service at
this point because the SQL service won’t start since the new directory is empty.
4 - Stop SQL Service
Stop the SQL service and move the master database files to the new directory.
sudo systemctl stop mssql-server.service
After you run the command, validate that the service is stopped.
sudo systemctl status mssql-server.service
5 - Move Master database files to the new directory
Now, we need to move the master database files from the default directory (/var/opt/mssql/data)
to the new directory /sqlsystemdb. The following command moves master.mdf and mastlog.ldf
to the new directory using the
sudo mv /var/opt/mssql/data/master.mdf /sqlsystemdb sudo mv /var/opt/mssql/data/mastlog.ldf /sqlsystemdb
Verify the files and permissions using the
ls -lrt command in the terminal.
We modified the master database file directory using the mssql-conf tool and moved those files (MDF, LDF). We need to start the SQL Service, connect to the SQL instance, and verify that our master database should be pointing to the new directory.
6 - Start SQL Service
Run the script in the terminal. It should return the status as Active (running) along with the service start timestamp.
sudo systemctl start mssql-server
7 - Validate master database directory
You can use SQL Server Management Studio (remotely), Azure Data Studio, or SQLCMD command-line utility for connecting to SQL Server and verifying the location.
As shown below, both master.mdf and mastlog.ldf files are in the /sqlsystemdb directory.
Once we have modified the master database files directory using the mssql-conf utility, SQL Server looks for master.mdf and mastlog.ldf in the new directory. If it cannot find the files, it creates a copy of the master data file in the specified directory and starts SQL Server. However, the template copy does not contain logins, certificates, user databases, encryption keys, SA passwords. Therefore, you should be careful and move your original master database files to a new directory to continue using SQL Server on Linux with the existing configuration.
Steps to move msdb, model, and tempdb databases for SQL Server on Linux
The steps to move the remaining system databases – model, msdb, and tempdb are similar to the Windows based SQL Server. The high-level steps of moving these databases are as below.
- Note the existing database files names and their path using the
sp_helpfilestatement in the corresponding database.
- Use the ALTER DATABASE MODIFY FILE statement to modify the system catalog for both MDF and LDF files.
- Stop SQL Services
- Move MDF and LDF files to a new directory except for TempDB since these will be recreated on startup of SQL Server.
- Start SQL Services
- Validate move
For this article, let’s move all remaining system databases to the directory /sqlsystemdb. The following section gives scripts for moving msdb, model, and tempdb.
1 - Find Current Path for Each Database File
First we use the
sp_helpfile for each database to find the file
names and current location
for each file that is used for these databases model, msdb and tempdb.
2 - Alter Databases for New Locations
Move msdb database to new location
Below is the script that configures SQL Server for the msdb files to the new location.
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData, FILENAME = '/sqlsystemdb/MSDBData.mdf'); GO ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog, FILENAME = '/sqlsystemdb/MSDBLog.ldf'); GO
Move model database to new location
Below is the script that configures SQL Server for the model files to the new location.
ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = '/sqlsystemdb/model.mdf'); GO ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = '/sqlsystemdb/modellog.ldf'); GO
Move tempdb database to new location
Below is the script that configures SQL Server for the tempdb files to the new location. Note there may be more files configured then just these two. The sp_helpdb command will provide output for all files that exist.
ALTER DATABASE tempDB MODIFY FILE ( NAME = tempdev, FILENAME = '/sqlsystemdb/tempdb.mdf'); GO ALTER DATABASE tempDB MODIFY FILE ( NAME = templog, FILENAME = '/sqlsystemdb/templog.ldf'); GO
3 - Stop SQL Service
At this point we can stop the SQL Server service using the same command we used before.
sudo systemctl stop mssql-server.service
4 - Move Database Files for msdb and model
This is the Linux command to move the msdb files to the new location.
mv /var/opt/mssql/data/MSDBData.mdf /sqlsystemdb mv /var/opt/mssql/data/MSDBLog.ldf /sqlsystemdb
This is the Linux command to move the model files to the new location.
mv /var/opt/mssql/data/model.mdf /sqlsystemdb mv /var/opt/mssql/data/modellog.ldf /sqlsystemdb
5 - Start SQL Service
At this point we can start the SQL Server service using the same command we used before.
sudo systemctl start mssql-server
6 -Validate New Locations
Run the following T-SQL script to validate location for all system databases – master, model, msdb and tempdb that they are in the new directory /sqlsystemdb.
In this article, we have discussed moving system databases from their default location to a different directory. If you do not have the free space on your system drive, it is good to move system databases to a different location so that the database files do not get filled up quickly. The master database requires mssql-conf utility, and MSDB, Model, TempDB requires changes in the system catalog using ALTER DATABASE MODIFY FILE statement.
- Check out more SQL Server and Linux tips.
- Refer to Microsoft documentation on SQL Server on Linux.
About the author
View all my tips
Article Last Updated: 2021-12-23