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.
This update is included in the following cumulative update for SQL Server:
After installing SQL server 2017 CU4 you will be able to change the location of the master database file and error log file location.
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