How to Move Master Database to Another Location – SQL Server On Linux

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/dataNow, 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.

Leave a comment

Your email address will not be published. Required fields are marked *