Resource Database

Resource Database

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

Resource Database

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.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the Resource database with the older version.

Physical Properties of Resource

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

Backing Up and Restoring the Resource Database

SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.

Important:
After restoring a backup of mssqlsystemresource.mdf, you must reapply any subsequent updates.

Accessing the Resource Database

The Resource database should only be modified by or at the direction of a Microsoft Customer Support Services (CSS) specialist. The ID of the Resource database is always 32767. Other important values associated with the Resource database are the version number and the last time that the database was updated.

To determine the version number of the Resource database, use:

SELECT SERVERPROPERTY('ResourceVersion');
GO

To determine when the Resource database was last updated, use:

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO

To access SQL definitions of system objects, use the OBJECT_DEFINITION function:

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));
GO

Tags

: Add a tag

Flag as ContentBug

documentation clarification

angel_III ... Thomas Lee | Edit | Show History

http://msdn.microsoft.com/en-us/library/ms190940.aspx
and this current document
http://msdn.microsoft.com/en-us/library/ms345408.aspx
say both, in regards to the resource database:
the default location of ... is ...

however, it seems clear that:
The database cannot be moved.

so, I strongly suggest to remove the term default from those sentences.
otherwise, it implies that you can move the database.

unless you clarify that you could move the resource database, in which case, you should put the link of
http://msdn.microsoft.com/en-us/library/ms345408.aspx into http://msdn.microsoft.com/en-us/library/ms190940.aspx
after the sentence telling the default location, and in http://msdn.microsoft.com/en-us/library/ms345408.aspx change the section for moving the resource database on how to do it.

Best Regards,
Guy Hengel, MVP SQL Server

Thanks for pointing that out. The topic has been fixed and will be published with this correction at the end of June.
Gail Erickson [MS] SQL Server Documentation Team

Concepts

Other Resources

Help and Information