SQL Server: Resource Database

Resource Database

In this Post I am going to take you through the 5th system database which is available right from SQL Server 2005. As we know till SQL Server 2000 there are four system databases available and they are master, model, msdb and tempdb databases. In SQL Server 2005 Microsoft has introduced a new system database to the SQL database engine called Resource database (real name for this database is mssqlsystemresource). This database is hidden to the users. 

Why Microsoft Introduced Resource DB?

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. 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 overwrite 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 you’ll find physical files for Resource database under the data directory <drive>:\Program Files\Microsoft SQL Server\MSSQL1.<instance_name>\Binn\. There’ll be two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf.

Can I take the backup of Resource database?

It is not possible to backup the database using BACKUP T-SQL statement since the database is always in a hidden state. 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.

Is there any way by which I can take a look inside Resource DB?

I don’t think there is any use in accessing this database for users. But if you want to know what’s inside this database you can use the below mentioned technique. This is Just for testing purpose; you shouldn’t modify anything and overwrite the resource database physical files.

Accessing the resource database is not possible in normal mode because the database will be in hidden state. However there are two ways to access this database.

1. By attaching physical files.
2. By running SQL Server in single user mode.

• Attaching Physical files 

1. Stop SQL Server Services
2. Copy the physical files (mssqlsystemresource.mdf and mssqlsystemresource.ldf) of this database to another location.
3. Start SQL Server Services
4. Attach the database as a user database (you can provide any name while attaching this DB)

sp_attach_db 'sarab_ResourceDB_test1','c:\db_dump\mssqlsystemresource.mdf','c:\db_dump\mssqlsystemresource.ldf'

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

• Running SQL Server in single user mode

Let’s try to access the Resource 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.

You can see from the above image that the database is in read only mode.

In normal mode we can get only two values for the Resource database. They are version number and the last update Date Time for resource database.

Get the version no. of Resource DB:

SELECT SERVERPROPERTY('ResourceVersion')   à 9.00.1399

Last Update Date Time:

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime') à 2005-10-14 01:56:22.007

APPLIES TO
MS SQL Server 2005
MS SQL Server 2008

Reference Taken from: http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/13/geek-city-the-resource-database.aspx

Thanks Kalen.

-- 
Thanks,
Sarabpreet Singh