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.
- By attaching physical files.
- By running SQL Server in single user mode.
- Attaching Physical files
- Stop SQL Server Services
- Copy the physical files (mssqlsystemresource.mdf and mssqlsystemresource.ldf) of this database to another location.
- Start SQL Server Services
- 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.
Regards
Sarabpreet Anand
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
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.
Thanks,
Sarabpreet Singh
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
Good info. I was already aware of Resource db which contains system objects but Attaching physical files was new for me.
Thanks…
For moving or copy the resource database no need to stop the instance. With out stoping the instance you can do it. Every thing is almost correct ehat you have given except this.
Nice Article …..Sir
Hi,
Database id=32767. It is not entry in Master.sys.databases.
Copy:- copy using Xcopy
It is updated when service pack applied or rollback.