How to Move SQL Server System Databases to a different directory on Linux

By:   |   Updated: 2021-12-23   |   Comments   |   Related: > SQL Server on Linux


Problem

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.

Solution

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.

linux command line

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.

sql version

Make sure the SQL Service is active (running). You can validate the status using this command.

systemctl status mssql-server
linux get sql service status

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.

  1. Create new directory
  2. Assign permissions
  3. Change location of master files
  4. Stop SQL Server
  5. Move database files
  6. Start SQL Server
  7. 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.

linux create new directory

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/
linux assign permissions

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.

  1. It sets up the SA password, accepts license agreement during the initial SQL Server on Linux configuration.
  2. It can be used to configure (set/unset) the SQL Server configuration values for:
    • 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.

  1. filelocation.masterdatafile: this sets the master database primary data file (MDF) location.
  2. 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
linux set master file locations 
sudo /opt/mssql/bin/mssql-conf set filelocation.masterlogfile /sqlsystemdb/mastlog.ldf
linux set master file locations

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
linux stop sql 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 mv command.

sudo mv /var/opt/mssql/data/master.mdf /sqlsystemdb 
sudo mv /var/opt/mssql/data/mastlog.ldf /sqlsystemdb
linux move files

Verify the files and permissions using the ls -lrt command in the terminal.

linux get directory list

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
linux start sql service

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.

exec sp_helpfile

As shown below, both master.mdf and mastlog.ldf files are in the /sqlsystemdb directory.

sql server sp_helpfile

Important Note

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.

  1. Note the existing database files names and their path using the sp_helpfile statement in the corresponding database.
  2. Use the ALTER DATABASE MODIFY FILE statement to modify the system catalog for both MDF and LDF files.
  3. Stop SQL Services
  4. Move MDF and LDF files to a new directory except for TempDB since these will be recreated on startup of SQL Server.
  5. Start SQL Services
  6. 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
alter database files settings

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
alter database files settings

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
alter database files settings

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.

check sql server database file locations

Summary

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.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rajendra Gupta 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


Article Last Updated: 2021-12-23

Comments For This Article

















get free sql tips
agree to terms