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.
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.
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.
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.
Now you have attached the Resource database to SQL server as a user database. You can now start exploring this database.
Let’s try to access the Resource database through single user mode.
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 TOMS SQL Server 2005MS SQL Server 2008
Reference Taken from: http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/13/geek-city-the-resource-database.aspx
-- Thanks,Sarabpreet Singh
Sarabpreet is SQLServer MVP, DBA, Speaker, Trainer, Blogger and Community Lead. You can find him speaking at a local UG Event or a SQL Webcast. He has 8+ years of Experience and worked with Industry Leaders like Wipro, HP and HCL. He has many SQL Certifications under his belt. His core competency lies in administration of SQL Server. Always ready to help, online \offline. His life’s mantra is “Knowledge Increases by sharing so, Pass it on”. To know about his speaking engagements visit: here...
Follow Sarab on
a good article sarab....i tried the above mentioned steps..it worked fine....
The only thing that motivates a Writer is a word of appreciation.
Sarab, I enjoyed your informative article. I copied a database SQL 2008 and all system, resource, and user database to a new Server SQL 2008 R2. Tried to start up database and received an error requiring "UPGRADE" of my resource database.... the error message expecting differerent version 661 or 655 I cannot recall from memory. But failed to "upgrade" ... after I used the 2008 R2 resource database... I started up all services (placing all DB mdf,ldf, ndf in appropriate file paths) .. everything started. I wonder if that equals an "UPGRADE" of the resource database.
Hi, this is very good one. what I understand here is, If i want to roll back my SP's then can i restore my resource DB back with older version?
It's a very good article. Thanks to Sarab
Leave a comment