Resource Database

SQL Server 2005 introduced a new read-only, hidden system database named Resource (RDB). The Resource database contains copies of all system objects that are shipped with SQL Server 2005.

Resource Database Overview :

The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects,System stored procedures and functions are stored here.The Resource Database does not contain any user data or any user metadata.By design, the Resource database is not visible under SQL Server Management Studio’s Object Explorer,or by using commands such as sp_helpdb and sys.databases.The physical file names of the Resource database is mssqlsystemresource.mdf and mssqlsystemresource.ldf.The important thing to be noted is that each instance of SQL Server has one and only one associated mssqlsystemresource.mdf & mssqlsystemresource.ldf file and that instances do not share this file.The ID for the Resource Database will be always 32767. The DBA shouldn’t rename or move the Resource Database file. If the files are renamed or moved from their respective locations then SQL Server will not start. The other important thing to be considered is not to put the Resource Database files in a compressed or encrypted NTFS file system folders as it will decrease the performance and will also possibly  prevent upgrades.

Resource Database File Location in SQL Server 2005 :
In SQL Server 2005 the Resource Database related MDF and LDF files will be available in <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ directory. The important thing to be noted is that the Resource Database related MDF & LDF file need to be available in the same directory where the Master Databases MDF & LDF files are located. By default during the installation of SQL Server 2005 both the Resource and the Master database files will be available in the same \Data directory.

Advantages of Resource Database :
In previous versions of SQL Server whenever service packs are applied all the system objects that are residing within the system and user databases gets updated which makes it very difficult to rollback the changes.
The only way to rollback the changes is to uninstall the instance of SQL Server and reinstall SQL Server followed by applying any Service Packs or Hotfixes to revert it to the previous version of the SQL Server Instance.
In SQL Server 2005 onwards the changes will be made to the Resource Database, in reality reflect the changes in all the system and user database of the particular instance.
If the DBA needs to apply a Service Pack to multiple instances, then the mssqlsystemresource.mdf and mssqlsystemresource.ldf needs to be copied by the DBA to the target instance.
If the DBA needs to rollback the changes performed by the Service Packs then they need to replace the Resource Database related MDF and LDF files with that of the previous backed up version

TSQL Query to determine the version of Resource Database:

SELECT SERVERPROPERTY('ResourceVersion')

TSQL Query to determine when the Resource Database was last backed up:
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

How to Backup Resource Database:
SQL Server does not allow you to take the backup of the Resource Database using Transact SQL code or by using SQL Server Management Studio. In order to take the backup of Resource Database you need to perform a file based or a disk based backup of the Mssqlsystemresource.mdf and Mssqlsystemresource.ldf files. The important thing to be noticed is that SQL Server will allow Resource Database related MDF & LDF files to be copied even when SQL Server is running. When the SQL Server Service is running it will only allow the Resource Database related MDF and LDF files to be copied, however it will not allow you to copy any other databases MDF or LDF files.

How to Restore Resource Database:
SQL Server does not allow you to restore Resource Database using Transact SQL code or by using SQL Server Management Studio. The only way to restore the Resource Database is to copy the Mssqlsystemresource.mdf and Mssqlsystemresource.ldf files to the respective folders. As part of the disaster recovery process it is very important for a DBA to document the location of Master and Resource databases. In a scenario when a DBA decides to rebuild the SQL Server 2005 instance, the DBA needs to manually copy the Resource Database to the respective folder locations prior to the restore of Master Database. If you don’t have the latest backup of Resource Database then you need to reapply all the subsequent updates before you can bring you environment back to what it was prior to disaster. So the best approach is to daily take backups of the system and user databases.
Size of Resource Database :
You can see the Size of Resource Database in a default Data directory of SQL Server 2005 Instance.

How to check Resource Database ID :
Hi Iam mentioning a little tip to the readers of my blog how to check Resource Database ID.

Please start the Server in Single User mode by using -m startup parameter. It turns out that even in single user mode, you still cannot see this hidden database. If you select from sys.databases, you will not see the database. But you can still USE the database:

You can verify you are in the Resource database, by running the following:
SELECT db_name()

After that if you want to see list of all databases in server run

select * from master.sys.sysdbreg and you will get the output like below picture.

Hi one more tip Iam posting here this is for who want to see the contents of resource database.
If you have a intention to see the contents of mssqlsystemresource, a couple of methods are available.
The easiest, if you just want to see what’s there, is to stop SQL Server,make copies of the two files for
the resource database, restart SQL Server, and then attach the copied files to create a database with a new name.You can do this by using Object Explorer in Management Studio or by using the CREATE DATABASE FOR ATTACH syntax to create a clone database, as shown here:

CREATE DATABASE resource_COPY 

ON (NAME = data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

        \mssqlsystemresource_COPY.mdf'),

   (NAME = log, FILENAME =

    'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource_COPY.ldf')

    FOR ATTACH;