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/)