In SQL Server 2005 a new method of handling system objects was introduced. The Resource database is a hidden system databse which is unique per each instance of SQL Server 2005 / 2008. This database contains all system objects. Logically these objects appear in the sys. schema of each database. The Resource Database does not contain any user data or metadata. The filenames for the Resource Database are called mssqlsystemresource.mdf and mssqlsystemresource.ldf.
In SQL 2005 the database is located in <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ folder. During the installation, the database will be in the same folder as the master database.
In SQL 2008 the Resource Database is located in <drive>: \Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn folder. Note that in SQL 2008 it is not mandatory to keep the master database in the same location as the Resource Database.
In a clustered environment, the Resource Database is located in the \Data folder on a shared disk drive. The ID for the Resource Database will be always 32767.
Benefits of the Resource Database structure:
- before SQL 2005 the only way to undo the applied changes during a service pack installation was to re-install the instance and apply all the service packs / hotfixes up until that point.
- in SQL 2005 / 2008 it is much easier to replicate or undo changes made by the hotfix / SP update, since the changes are made only to the Resource Database, which in its turn affect the objects on the sys. schema of the particular instance.
- If hotfix / SP changes need to be introduced to multiple instances, the DBA needs to copy over the upgraded mssqlsystemresource.mdf and mssqlsystemresource.ldf files.
- If the changes need to be r,olled back, the DBA needs to copy back the previous versions of the mssqlsystemresource.mdf and mssqlsystemresource.ldf files.
To query the version of the Resource Database, execute the following code:
SELECT SERVERPROPERTY('ResourceVersion')
GO
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO
How to backup the Resource Database: you may not backup the database from SSMS or by using T-SQL. You can backup the database by copying the physical files of the database. You can copy the files even if the SQL Server instance is running. (you are not able to copy any other database files while the SQL Server instance is running.)
How to restore the Resource Database: you may not restore the database from SSMS or by using T-SQL. The DBA has to manually copy the files in the respective folders. In a disaster recovery situation, the Resource Database has to be copied to the appropriate location prior to restoring of the master database. Otherwise, the DBA will have to apply all changes by hotfixes / SP before you can bring up the instance as it was prior to the disaster.
Disaster recovery planning: Make sure to include the Resource Database backups in your disaster recovery planning.
