Systems Engineering and RDBMS

Moving the Resource Database in SQL Server 2005

Posted by decipherinfosys on February 12, 2009

As we had discussed in one of our previous blog post, SQL Server 2005 introduced a new system database called the Resource Database.  In response to that post, a reader asked us how he can go about moving the resource database because he had a need to move the master database location and when you do that, you have to move the resource database as well to the same location since it depends upon the physical location of the master database.  Typically, you would not be moving the system databases – however, at times because of relocation because of planned disk maintenance or planned relocation to another RAID array or for failure recovery you might have a need to do it.

In SQL Server 2005, in order to move the resource database, first you need to make sure that you stop the SQL Server service and then 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.  After this, the move is the same as others by using the ALTER DATABASE 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’)

And once the move has been done, then stop and start the SQL Server service again – this time without those startup option flags and without the trace flag and this time around, the new location will be used for the resource database files.

Please do note that this behavior has changed in SQL Server 2008.  In SQL Server 2008, the resource database now resides under:

“X:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn” and this location cannot be changed i.e. the Resource database cannot be moved in SQL Server 2008.

About these ads

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 80 other followers

%d bloggers like this: