A note on Resource database (mssqlsystemresource ) of SQL Server

Most of us saw two tiny files named mssqlsystemresource.mdf and mssqlsystemresource.ldf in our SQL Server (typically in the same directory where other system database files are residing) but probably never had to work on them. So as per me, these files (and of course which means resource database itself) is some how less understood by many of us compared to other systems database.

Resource database is actually another system databases present in SQL 2005 onwards which is a very vital part of SQL Server. It is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects (eg. sys.objects) physically reside in the Resource database, but they logically appear in the sys schema of every database. The Resourcedatabase does not contain user data or user metadata.

One of the major advantages of Resource database is upgrading to a new version of SQL Server an easier and faster. In earlier versions of SQL Server, upgrading required dropping and creating system objects by setup. Now as 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 by setup. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the Resource database with the older version.

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. Each instance of SQL Server has one and only one uniquely associated mssqlsystemresource.mdf file. Also 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 even to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually (simply by copy paste) but you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.

Some most important values associated with the Resource database are the version number and the last time that the database was updated. This can be very helpful for anyone wishing to manually restore resource database for an instance. However please be advised manually restoring resource database is an extremely dangerous operation. Ideally 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.

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
So to conclude, resourcedatabase should be treated as another system database only and every effort should be made to avoid manually modifying it.  

Note: Reference is taken from http://msdn.microsoft.com/en-us/library/ms190940(SQL.90).aspx on this note.