KB4053439 - Improvement: Move master database and error log file to another location in SQL Server 2017 on Linux

Symptoms

Assume that you use SQL Server 2017 on Linux. By default, master database is located at the location /var/opt/mssql/data. Now, you want to change the location of master database file and error log file to a different location.

Resolution

This update is included in the following cumulative update for SQL Server:

       Cumulative Update 4 for SQL Server 2017

After installing SQL server 2017 CU4 you will be able to change the location of the master database file and error log file location.

More Information

Configure Errorlogfile location:

• Use the following command to set new errorlog location. Ensure that the folder structure is pre-created and ‘mssql’ account has permissions to create file.

  • ./mssql-conf set filelocation.errorlogfile /home/user/sqlserver/log/errorlog

  • The environment variable for error log is MSSQL_ERROR_LOG_FILE.

Following are the scenario’s and steps to configure location of master data base files:

NOTE: Please make sure that you take complete backup of all your databases including master database before making any of the changes.

SCENARIO 1: change the location of master database file on existing SQL installation.

• New options are available in mssql-conf utility to provide the location of master.mdf , mastlog.ldf and errorlog.

• Following are the commands to set the location.

• ./mssql-conf set filelocation.masterdatafile /home/user/sqlserver/data/master.mdf

• ./mssql-conf set filelocation.masterlogfile /home/user/sqlserver/data/mastlog.ldf

• Prior to configuring the settings, ensure that the path provided already exists and 'mssql' user has full permission on the folder.

• After configuring the setting stop mssql-server (systemctl stop mssql-server) and move master.mdf and mastlog.ldf to the configured location and restart sql server (systemctl start mssql-server).

SCENARIO 2: Pre-Configure location of master database file before installation.

• After you install mssql-server (ex: apt-get install mssql-server).

• You can setup environment variable as follows:

• export MSSQL_MASTER_DATA_FILE=/home/user/sqlservr/data/master.mdf

• export MSSQL_MASTER_LOG_FILE=/home/user/sqlservr/data/mastlog.ldf

• Or you can use the steps mentioned in SCENARIO 1 to set configuration, then change permission of /var/opt/mssql/ folder that is owned by mssql account. If you set Environment variable and mssql-conf settings then environment variable will take precedence.

NOTE: File name of the master data base should be 'master.mdf' and master log should be 'mastlog.ldf' during installation. The filename may be changed later.

If the folder structure is not created or mssql account does not have right permission then you will get following error during setup.

SCENARIO 3: change the name of master database files

• Stop mssql-server.

• Set the filenames using mssql-conf

• Rename master database files to the configured file names.

• Start mssql-server

Following are the commands to change the name of master database files.

References

Learn about the terminologythat Microsoft uses to describe software updates.