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 :\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 : \Program Files\Microsoft SQL Server\MSSQL10.\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 : \Program Files\Microsoft SQL Server\MSSQL10.\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');

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, by treating the files as if they were like any other binary files, instead of a database file. 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 or SQL Server 2008 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.

Conclusion
It’s very important for a DBA to include the Resource Database as part of the Disaster Recovery Document and they should ensure that Resource Database is part of daily back up plan along with other system and user databases.