With the release of SQL Server 2017 CU4, you can use the mssql-conf utility to move the master database file to another location. By default, master database is located at the location /var/opt/mssql/data. Now, if you want to change the location of master database file to a different location, you can follow the below steps to do that;

Change the location of master database file to a different place

Let’s assume that you want to move the master database files from default location to the location “/var/newmas”

• Verify the default location of the master database files.

• Create a newmas folder at “/var/” location and verify it

Syntax: sudo mkdir /var/newmas (It will create the directory newmas)
Syntax: ls -l  

• Since the created folder is owned by root – user & group user, it can’t be accessed by a non-root user. You need to change the ownership of the folder to mssql by running following commands;

Syntax: sudo chown mssql /var/newmas (It changes the user ownership)
Syntax: sudo chgrp mssql /var/newmas (It changes the group user ownership)

• Fire the below commands to set the new location for master database files

Syntax: /opt/mssql/bin/mssql-conf set filelocation.masterdatafile /var/newmas/master.mdf
Syntax: /opt/mssql/bin/mssql-conf set filelocation.masterlogfile /var/newmas/mastlog.ldf

• Use the following command to STOP the SQL Server service

Syntax: sudo systemctl stop mssql-server

• Copy and paste the master database files to new location

Syntax: cp /var/opt/mssql/data/master.mdf /var/newmas/
Syntax: cp /var/opt/mssql/data/mastlog.ldf /var/newmas/

• Navigate the directory /var/newmas and verify the files have been copied successfully or not.

From the above image, we can clearly see that the copied files owned by root user & user group, but not by mssql user & user group.

• Let’s change the owner of both files from root to mssql and verify the details

–Data file
Syntax: sudo chown mssql /var/newmas/master.mdf
Syntax: sudo chgrp mssql /var/newmas/master.mdf
–Log file
Syntax: sudo chown mssql /var/newmas/mastlog.ldf
Syntax: sudo chgrp mssql /var/newmas/mastlog.ldf

• START and check the STATUS of SQL Server service 

Syntax: sudo systemctl start mssql-server
Syntax: sudo systemctl status mssql-server

• Verify the new location of the master database files

Hope, you enjoyed relocating of master database files on SQL Server on Linux.

The post How to Move Master Database to Another Location – SQL Server On Linux appeared first on .