Home » CodeProject » Move or Relocate the files of Resoruce Database in SQL Server 2005

Move or Relocate the files of Resoruce Database in SQL Server 2005


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

About these ads

3 Comments

  1. 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.

  2. 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.

  3. GS test says:

    Move or Relocate the files of Resoruce Database in SQL Server 2005 « MSSQLFUN

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 228 other followers

%d bloggers like this: