Moving the Resource Database in SQL Server 2005

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.