Symptoms

Assume that you use SQL Server 2017 on Linux. By default, master database is located at the location /var/opt/mssql/dataNow, 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.

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

Latest cumulative update for SQL Server 2017

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.

ERROR: BootstrapSystemDataDirectories() failure (HRESULT 0x80070002)

Initial setup of Microsoft SQL Server failed. Please consult the ERRORLOG

 

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.

systemctl stop mssql-server

./mssql-conf set filelocation.masterdatafile /home/sqlservr/data/testmaster.mdf

./mssql-conf set filelocation.masterlogfile /home/sqlservr/data/testmastlog.ldf

mv /home/sqlservr/data/master.mdf /home/sqlservr/data/testmaster.mdf

mv /home/sqlservr/data/mastlog.ldf /home/sqlservr/data/testmastlog.ldf

systemctl start mssql-server

References

Learn about the terminologythat Microsoft uses to describe software updates.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×