What is importance of Resource DB in SQL Server?

What is Resource Database and what is holds ?

The resource database (mssqlsystemresource) database is the new system databases available in SQL Server 2005 and above.
The Resource database is a read-only, hidden system 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.

Why Resource Database is important?

The Resource database appears to be a critical system database as the SQL Server service is now dependent on this.

What are the Advantages of Resource Database?

Prior to SQL Server 2005, 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 back to the previous version of the SQL Server Instance and attached all User databases.

•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

Here is some information about the resource database.

•This hidden database stores system stored procedures and functions

•This was created by Microsoft in order to facilitate faster upgrades. If the system objects were to be upgraded in any Service Pack, just replacing the resource database is enough.

•This database does not list when sp_helpdb is executed.

•Since resource database is hidden, it cannot be backed up using the Backup Database command. The only way to backup this database is to copy the .mdf & .ldf files. Same holds good for restoring this database. If resource database is restored, all the service packs/hotfixes need to be  reapplied.

•The database id 32767 is reserved for resource database and cannot be changed. If in SQL Server 2000, if any database is allocated the ID 32767, the upgrade to SQL Server 2005 will fail.

•If the files are renamed or moved from their respective locations then SQL Server will not start

•The physical file names of the Resource database is mssqlsystemresource.mdf and mssqlsystemresource.ldf, which are specific to each SQL Instance.

•The location of mssqlsystemresource.mdf/.ldf files are different for SQL Server 2005 & SQL Server 2008. ◦In SQL Server 2005 these files are located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ folder. These files must be in the same folder where the data files of master database are stored.

◦In SQL Server 2008 the default location of these files is C:\Program Files\Microsoft SQL Server\MSSQL10.\MSSQL\Binn. These files need not be on the same directory where master database files are located.

◦In a clustered environment, the Resource database exists in the \Data folder on a shared disk drive.

How to Backup Resource Database ?

In order to take the backup of Resource Database we need to perform a file based or a disk based backup of the Mssqlsystemresource.mdf and Mssqlsystemresource.ldf files, by copy paste method. There is no Full backup or online backup for this database.

SQL Server will allow Resource Database related MDF & LDF files to be copied even when SQL Server is running.

How to Restore Resource Database ?

Use Copy Paste Method only, overwrite the existing files from the backup location.

How to check Current Version of resourcedb and when the last updated time of ResourceDB?

Use the following TSQL Code, to get this info

SELECT SERVERPROPERTY('ResourceVersion') Resource_DB_Version,
SERVERPROPERTY('ResourceLastUpdateDateTime') Resource_DB_LastUpdate_DateTime
GO

Notes:

1.Microsoft Direct on Resource Database (http://msdn.microsoft.com/en-us/library/ms190940.aspx)

2.Do not put the Resource Database files in a compressed or encrypted NTFS file system folders as it will effect performance and will also possibly  prevent upgrades.