Importance of the 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 and SQL Server 2008.

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 which are physically available only in the Resource database, even though they logically appear in the SYS schema of every database. 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 | Databases | System Databases Node. 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. In a clustered environment, the Resource database exists in the \Data folder on a shared disk drive. 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 hinder 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.

Resource Database File Location in SQL Server 2008
In SQL Server 2008 the Resource Database related MDF and LDF files will be available in <drive>: \Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn. The important thing to be noted is that the Resource Database related MDF & LDF file are in the \Binn directory and the Master Databases MDF & LDF files will be located in the <drive>: \Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Data directory. In SQL Server 2008 it is not mandatory to keep both the Resource and Master Database files in the same 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, which will indeed 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')
GO
TSQL Query to determine when the Resource Database was last backed up
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO