Resource Database

In this article we are going to take a look at the 5th system databases available right from SQL Server 2005. As everyone know till SQL Server 2000 there are four system database is available and they are master, model, msdb and tempdb databases. From SQL Server 2005 Microsoft has introduced a new system database to the database engine called Resource database (actual name for this database is mssqlsystemresource ) which is hidden to the users.

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 schemas 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 <drive>:Program FilesMicrosoft SQL ServerMSSQL10.<instance_name>Binn. You can find two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf in the directory mentioned above.

Can I backup the database?

It is not possible to backup the database using BACKUP t-sql statement since the database will be in hidden state. Since backup is not possible obviously you cant restore the database. We have only one way to take the backup of this database is to copy the physical files of the database. You can stop the SQL server, copy the physical files of this database to another location and start the SQL Service. Now you can have the backup of Resource database physical files, you can just replace the physical files from the backup taken in case needed.

How to access Resource database?

I dont think there is any use in access this database for users, since users are curious about whats inside this database Im writing this. Since this is for testing purpose, please dont modify anything and overwrite the resource database physical files. Accessing the resource database is not possible in normal mode since the database will be in hidden state. There are two ways to access this database.

• Attaching Physical files
• Running SQL Server in single user mode

Lets discuss one by one

Attaching Physical files

• Stop SQL Server Services
• Copy the physical files (mssqlsystemresource.mdf and mssqlsystemresource.ldf) of this database to another location. In this test Im going to copy the database to C:Temp folder
• Start SQL Server Services
• Attach the database as a user database (you can provide any name to the database) from the physical file copied.

sp_attach_db 'Resource',
'c:\temp\mssqlsystemresource.mdf',
'c:\temp\mssqlsystemresource.ldf'

Now you have attached the Resource database to sql server as a user database. You can now start exploring the database.

Running SQL Server in single user mode

In this method you can directly access the resource database however to do this you need to start SQL server services in single user mode. Lets access the database through single user mode.

• Start the SQL Services in single user mode.

 

• Now connect to the server and access the database as shown below.

 

Thats it start exploring the database. You can see from the above image that the database is in read only mode.

Please note that this is for testing purpose only, please dont modify anything in the database unless you know what you are performing or as directed by Microsoft customer support services.

In normal mode we can get only two values for the Resource database. The are version number and the last update done for resource database. To get these values use the query below.

To know resource db version

SELECT SERVERPROPERTY('ResourceVersion');
GO

Output:

Resource db Version

=====================

9.00.4035

(1 row(s) affected)

To know last updated date

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO

Output:

Resource db updated date time

================================

2008-11-24 14:27:07.113

(1 row(s) affected)