How to Move Resource Database?


Resource Database: Resource database is available from the SQL Server 2005 and higher level versions. Resource database is read only and hidden database. Resource database contains all the system objects that shipped 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.

Resource database will be very useful in doing upgrades or in un-installing up-grades. In the previous versions of SQL Server up-grade needs creating/dropping of the system objects. From the SQL Server 2005 version upgrade is just procedure to copying resource database file to local server.

Name of Resource database data and log file.
mssqlsystemresource.mdf
mssqlsystemresource.ldf

Resource database data and log file location is same as the Master database location. In case if you are moving Master database you have to move the Resource database as well to the same location.

You can check the Resource database version and last up-grade time using the SERVERPROPERTY function.

SELECT SERVERPROPERTY(‘RESOURCEVERSION’);
GO
SELECT SERVERPROPERTY(‘RESOURCELASTUPDATEDATETIME’);
GO 

To move the resource database, you have to start the SQL Server service using either -m (single user mode) or -f (minimal configuration) and using -T3608 trace flag which will skip the recovery of all the databases other than the master database.

You can do it either from the Configuration manager or from the command prompt using below command.
Default Instance
NET START MSSQLSERVER /f /T3608
Named Instance
NET START MSSQL$instancename /f /T3608

Execute the below ALTER command once you have started the SQL Service by specifying the new location, location should be same as Master database location.

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= '\mssqlsystemresource.mdf')
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= '\mssqlsystemresource.ldf')
Advertisements

3 thoughts on “How to Move Resource Database?

  1. Lakshman

    hi Jugal.. what you said is , the location of resource database data and log file are at the same place where master database is located.
    in from sql server 2008 they are under %root directory%\binn folder. more over i am unable to move data and log file of resource database file.
    These are the steps i followed.
    –> i started the server in /m and /t3608 mode
    –>i fired the commands ” alter database mssqlsystemresource modify file(name=data, filename=’—-\mssqlsystemresource.mdf’)
    “alter databse mssqlsystemresource modify file(name=log, filename=’—-\\mssqlsystemresource.ldf’)
    and the queries executed successfully
    when i restart the sql server in normal mode i was unable to restart

    Thanks in advance for your suggestions

    Reply

Thanks for the comment, will get back to you soon... Jugal Shah

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