The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. It comes into picture from SQL Server 2005 onwards.
In SQL Server 2005, in order to move or relocate the files of the Resource Database :-
1) Stop the SQL Server service
2) Start it using either -m (single user mode) or -f (minimal configuration) startup option which will start it in the maintenance mode. In addition, use the -T3608 trace flag which will skip the recovery of all the databases other than the master database. By doing this, we are ensuring that there is no process using the Resource database.
3) After this, the move is the same as others by using the ALTER DATABASE command: Change the file location by below command.
ALTER DATABASE MSSQLSYSTEMRESOURCE MODIFY FILE (NAME=DATA, FILENAME= ‘<THE NEW PATH FOR THE DATA FILE>\MSSQLSYSTEMRESOURCE.MDF’)
ALTER DATABASE MSSQLSYSTEMRESOURCE MODIFY FILE (NAME=LOG, FILENAME= ‘<THE NEW PATH FOR THE LOG FILE>\MSSQLSYSTEMRESOURCE.LDF’)
4) Once above command completed, then stop the SQL Server service
5) Move the file or files to the new location.
6) Restart the instance of SQL Server, this time without those startup option flags and without the trace flag
Please do note that this behavior has changed from SQL Server 2008 onwards. Now, Resource database cannot be moved.
If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
From MSDN:
http://msdn.microsoft.com/en-us/library/ms345408%28v=sql.90%29.aspx
“The Resource database depends on the location of the master database. The Resource data and log files must reside together and must be in the same location as the master data file (master.mdf). Therefore, if you move the master database, you must also move the Resource database to the same location as the master data file.”
Just pointing it out – I have had to fix a server in the past where someone tried to move resource w/o moving master.
Thanks Andy for Reviews.
Yes, Its recommended to keep master & resource DB files at same location. Otherwise it will create issues at time of service pack upgrade.
Move or Relocate the files of Resoruce Database in SQL Server 2005 « MSSQLFUN