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
Syntax: sudo chown mssql /var/newmas/master.mdf
Syntax: sudo chgrp mssql /var/newmas/master.mdf
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.