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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn
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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLogERRORLOG;-lC:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmastlog.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.MSSQLSERVERMSSQLDATAmssqlsystemresource.mdf’);
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME = log, FILENAME = ‘Z:MSSQL10.MSSQLSERVERMSSQLDATAmssqlsystemresource.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
I noticed you quoted the resource database and master database files must be in same location from SQL 2005. However, you are moving SQL 2008 R2 master database. Please be aware that the requirement of resource database on 2005 and 2008 is different – http://msdn.microsoft.com/en-us/library/ms345408.aspx. In SQL 2008, the resource database should not be moved. – Just thought to share.
You’re right! I totally missed that the link was for 2005. However, the information is still good to know in case you have an instance that was upgraded from 2005. Something else interesting to note is that although you should not move the resource database, you can. The instructions in the post work for moving it even though it is not best practice or something that should be done.
this article is nice
but error is comming while moving master database
the error is: the request failed or the service did not respond in a timely fashon.consult the
event log or other applicable error logs for details.
could you please help me …
You are going to have to be much more specific. What are you doing when you get that error? Did you look in the event log and error log to see what you found?
When I go to the SQL Server Configuration Manager, and I select the SQL Server Services, I am not able to see the SQL Server Service or anything else. It is actually empty.
My nodes are SQL01 & SQL02, but the actual global (UNC) name is SQLPROD.
Why I cant see the SQL Server Service? Do I need to stop the cluster process before I try to change the parameters on the SQL Server Service?
It’s probably because the services are running on the other node. Log into the other node and open Configuration Manager from there. Alternately, you could use cluster manager and move the services group from the other node to the one you are on.
Thanks for your quick reply. This is the first time I am doing this movement. We are planning for outage in 2 weeks. So, I am building a document on preparation for the move of the system files to new and faster disks.
Forgot to mention. My SQL01 is my preferred owner and it is actually running. SQL01 is where I cant see the SQL Server Services when I go to Config Manager. Yes, I was expecting to see the services running but there is none.
SQL02 is the failover server. This is where I can see all the services and all services are stopped.
Any ideas?
You need to verify in Cluster Manager which node the SQL services are running on and also verify that they are running. Run configuration manager from that node.
do u have any link in youtube .because i tried three times still am getting any error
I do not have a YouTube video for this, but many folks have used this article successfully. If you still need help I’m available to consult just send me an email.
Thank you! this worked like a charm!