If you have been worked with SQL Server 2005 and SQL Server 2008, you must aware of the new system database called System Resource (aka Resource) database. It is a different type of system database when compared to other system databases in SQL Server such as Master, Msdb, Model and TempDB. All the system information pertaining to that specific instance is stored in the Resource database. When a service pack / hot fix is installed the resource database is updated.

There are some interesting points to be noted with respect to Resource Database.

1) It cannot be backed up / restored using general backup / restore procedures from inside SQL Server. It has to be manually backed up similar to a file backup on a windows server.

2) It has to be placed in the same directory where Master Database was placed, so when moving the system databases, Master in particular, the Resource Database has to be moved on to that location manually to have SQL Server start.

What is the use of this Database?

Resource database is a read-only and hidden database which physically contains all the system objects that accompanies with SQL Server. All the objects under sys schema’s are exists logically in each database. The Resource database does not contain user data or user metadata. The ID of the Resource database is always 32767.

The main purpose of Resource database is to make the update to the server in a fast and easy way. Since all the system objects resides under resource database, during the upgrade we can just over write the resource database files with the new resource db files which will update all the system objects exists in the database. In the old version of SQL all the system objects need to be dropped and recreated at the time of upgrade which is time consuming and the setup will fail if any one of the object gives some problem.

By default the physical files for Resource database exists under the default data directory :\Program Files\Microsoft SQL Server\MSSQL10.\Binn. You can find two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf in the directory mentioned above.

What new changes in SQL Server 2008 R2 edition for RESOURCE database

When you look for the same database in a SQL Server 2008 installation, you would be surprised that it does not exist anymore with the other system databases. In SQL Server 2008 the location of the Resource database has been changed to the Binary directory Binn. Due to this, when the master database is moved to a different location, there is no additional tasks related to resource database in SQL Server 2008.

-- To determine the version number of the Resource database, use:

SELECT SERVERPROPERTY('ResourceVersion');
GO

-- To determine when the Resource database was last updated, use:

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO

SELECT @@VERSION AS 'SQL Server Version'
GO

SERVERPROPERTY Proerties http://msdn.microsoft.com/en-us/library/ms174396.aspx

Thanks

Yogesh

1. Recently was encountered with a Developer saying the “sys” schema objects were no longer in the MASTER database . OR What is Resource Database?

There are following number of system databases in SQL server.

1. Master
2. Resource
3. TempDB
4. MSDB
5. Model
6. Distribution
7. ReportServer
8. ReportServerTempDB

As the question is based on resource, am mainly focusing on resource DB here as follows. Resource database is a read-only and hidden database. Resource DB physically contains all the system objects that accompanies with SQL Server. Logically all objects present in sys schema exist in each DB. The Resource database does not contain user data or user metadata.The Resource DB ID is always 32767.

By default you’ll find physical files for Resource database under the data directory :\Program Files\Microsoft SQL Server\MSSQL1.\Binn. There’ll be two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf.

How to take the backup and restore of Resource DB

SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.

2. what is the use of RESOURCE Database inside SQL Server?

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

(a) To allow for faster upgrades (upgrade is now a file copy instead of DROP and re-CREATE 1000s of system objects)

(b) To allow for rollbacks of QFEs (file copy semantics).

In SQL Server 2005 onwards service pack 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.

3. Tell us one change in RESOURCE Database inside SQL Server 2008 R2 edition?

In SQL Server 2005, the data and log files for the Resource database depend on the location of the data file of the master database. Therefore, moving the master database also requires moving the Resource database to the same location. In SQL Server 2008, this dependency does not exist. The master database files can be moved without moving the Resource database.

In SQL Server 2008, the default location of the Resource database is :\Program Files\Microsoft SQL Server\MSSQL10_50.\Binn. The Resource database cannot be moved.

4. How can we find the properties of the RESOURCE database like version etc?

SELECT SERVERPROPERTY('ResourceVersion') ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceLastUpdateDateTime

What is the Resource database?

The Resource database (shortly referred to as RDB) is a hidden, read-only 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. This is the reason why it does not appear in SQL Server Management Studio.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server

Database_ID 32767 is reserved Resource Database.

SQL Server maximum databases per instances can be created are 32,767. This last number has been reserved by Resource Database itself.

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf

Changes in SQL Server 2008 R2 edition for RESOURCE database

Location of this database is at different places in the different version of SQL Server.

In SQL Server 2008:

• :\Program Files\Microsoft SQL Server\MSSQL10.\Binn.
• The Resource database cannot be moved.

In SQL Server 2005:

• Same place where master database is located.
• The Resource database have to move with master database.

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

You can run following commands to know resourcedb version and last updated time.

SELECT SERVERPROPERTY('ResourceVersion') ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceLastUpdateDateTime

To access SQL definitions of system objects, use the OBJECT_DEFINITION function:

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));

http://blog.sqlauthority.com/2011/05/10/sql-server-resource-database-id-32767 http://blog.sqlauthority.com/2010/01/31/sql-server-location-of-resource-database-in-sql-server-editions

Sql 2008 have 5 system databases. Resource database newly introduced for making upgrade an easier and faster procedure.

The Resource database is a read-only 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.

ID of the Resource database is always 32767. Other important values associated with the Resource database are the version number and the last time that the database was updated.

To determine the version number of the Resource database, use:

SELECT SERVERPROPERTY('ResourceVersion');

GO

To determine when the Resource database was last updated, use:

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

GO

Resource Database

The Resource database is a read-only 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.

Change in SQL Server 2008 R2 as compare to prior versions

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.

Checking Properties of Resource Database

The Resource database should only be modified by or at the direction of a Microsoft Customer Support Services (CSS) specialist. The ID of the Resource database is always 32767. Other important values associated with the Resource database are the version number and the last time that the database was updated.

To determine the version number of the Resource database, use:

SELECT SERVERPROPERTY('ResourceVersion');
GO

To determine when the Resource database was last updated, use:

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO

Reference: MSDN