The catch to moving the Master database is that you must also move the Resource database. Microsoft states that the Resource database must reside in the same location as the Master database. You can see their instructions HERE on how to move the Master database.
However, on my cluster I did not find the Resource database in the same location as Master. In fact I didn’t find it there on any of the instances I have running on my laptop either. I actually found it in the local disk of each node in the cluster. Here is where I found mine, and if you don’t find yours there, then just do a search in all drives with Windows Explorer.
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
Now that we have the file location for the Resource database files we need to verify the file location of the master database as well. Here is the query we can use to get that:
SELECT physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N’master’);
GO
First we will focus on moving the Master database, so we need to tell SQL where we plan to move the master database files. SQL stores the location of the Master database in the registry, but it’s best to use SQL Server Configuration Manager to make the change. Open Configuration Manager and select the “SQL Server Services” node on the left. Next we want to right click and select properties on the SQL Server Service for the instance we are changing. In the properties dialog box we want to select the Advanced tab and take a look at the Startup Parameters option.
By default there are 3 startup parameters. “-d” specifies the location of the Master database data file. “-l” specifies the location of the Master database log file. “-e” specifies the location of the SQL Server error log file. Here is what it looks like on my laptop for the default instance.
-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
Go ahead and change the location of the Master database data and log file locations and click OK. You can also change the error log location at the same time if you need to.
We need to shut down SQL. Once SQL is stopped, copy the physical MDF and LDF to the new file system location. Remember when I said my resource database was in a local drive on my cluster nodes, even though the Microsoft article said it HAD to be in the same location as Master? If that is the case for you, then you can leave it alone and just go ahead and restart SQL Server. However, if your Resource database was indeed in the same place as Master then read on to see how to get that moved.
When we start SQL back up we now have to start it in recovery mode to change the location of the Resource database. We can do that by running a command prompt as an Administrator and running the following command. Type this command exactly as you see here.
NET START MSSQLSERVER /f /T3608
Next we need to tell SQL where we plan to move the Resource database files. Make sure to change the “FILENAME” path but leave the ”NAME” the same as what you see below.
USE master;
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME = data, FILENAME = ‘Y:\MSSQL10.MSSQLSERVER\MSSQL\DATA\mssqlsystemresource.mdf’);
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME = log, FILENAME = ‘Z:\MSSQL10.MSSQLSERVER\MSSQL\DATA\mssqlsystemresource.ldf’);
GO
Don’t shut down SQL yet. Go ahead and copy the physical MDF and LDF to the new file system location, and then run the following command. The system Resource database must be put back into a read only mode.
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
Now we can shut down and restart SQL server. Once SQL is back up you might want to run the first query again just to make sure everything went as planned and the location has been updated properly. Also, don’t forget to clean up after yourself and delete the old database files.
More Information – If you are moving your Master database you might want to check out the following posts:
How to move the Model database
How to move the TempDB database
