Resource Database common Questions

While working with customers, I get quite a few questions on Resource database. Hence I thought about answering some of them in this blog.

Some of the questions that I get:

• Can I access it through management studio?
• Should I backup my Resource database along with the other system database backups?
• Can I change the Resource database path? Should I?
• Can the size of Resource database increase over a period a time and with increased usage of the instance?
• How to determine the Resource Database version or the last updated date?
• How to restore the Resource database?
• What should I do if my Resource database gets corrupted?
• When does it get updated?
• How to detach Resource database?
• How to move Resource database?

Discussion in this blog Applies to: SQL 2008, SQL 2008 R2 and SQL 2012

(Note: SQL 2005 Resource database implementation is not included in this discussion; there are a few implementation differences in SQL 2005).

Introduction to Resource Database:

Before I start answering the questions, let me provide some background information on Resource database.

SQL Server has 4 visible system databases i.e. master, model, tempdb and msdb and it also contains a fifth, “hidden” database Resource Database that you will never see using any of the normal SQL commands that list all your databases. It is a read-only database that contains 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.

Also 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

Actual Name: This database is referred to as the resource database, but its actual name is “mssqlsystemresource”.

Physical File names and path: mssqlsystemresource.mdf and mssqlsystemresource.ldf

Located in: <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\

Questions on Resource Database:

Can I access it through management studio?

In the object explorer you would not be able to see the Resource db.

And if you try to access it through the script window, you will get the following error:

So now shutdown your SQL server instance and start the SQL instance in a single user mode:

Now connect using your management studio and play around with the Resource Database as much as you like :).

Should I backup my Resource database along with the other system database backups?

No. If fact you cannot do a SQL server backup of the Resource database. Treat your resource database as if it were a binary file and not a database file. You can do a manual file copy of the mssqlsystemresource.mdf file. However if for some reason the Resource database has been corrupted or is unusable or unavailable then using the Repair option in the SQL setup would be the preferred method to repair the SQL server installation. If for some reason you still end up having to restore a backup of mssqlsystemresource.mdf file then make sure that you reapply any subsequent service pack/hotfix updates.

Can I change the Resource database path? Should I?

No.

Can the size of Resource database increase over a period a time with increased usage of the instance?

As the Resource database does not contain user data or user metadata its size should not increase with usage of the instance. It would remain constant however it can change with upgrades and service pack installs.

How to determine the Resource Database version or the last updated date?

SELECT SERVERPROPERTY('ResourceVersion') ResourceDatabaseVersion,

SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceDatabaseLastupdatedate;

How to restore the Resource database?

Using the Repair option in the SQL setup would be the preferred method to repair the SQL server installation if Resource database is unavailable. Only other way to restore Resource database is to manually restore a backup of mssqlsystemresource.mdf file. However if you do this then make sure that you reapply any subsequent service pack/hotfix updates.

What should I do if my Resource database gets corrupted?

Refer to the answer for “How to restore Resource database” question.

When does it get updated?

During service pack installs and upgrades.

How to detach Resource database?

Cannot do a SQL detach/attach. You can do a manual file copy of the mssqlsystemresource.mdf file.

How to move Resource database?

Starting SQL 2008 Resource Database cannot be moved.

I will try to add more to his list later.

Thanks

Comments

• Anonymous
  October 09, 2013
  nice info ,thnx so much

• Anonymous
  October 14, 2013
  thank you so much for the information..

• Anonymous
  January 09, 2014
  how to make resource database visible ?????????????????

• Anonymous
  April 17, 2014
  Great it helps freshers like me ..keep posting !!!

• Anonymous
  June 18, 2014
  Gud info, one shot at a time!

• Anonymous
  September 24, 2014
  Thanx

• Anonymous
  November 04, 2014
  Nice presentation  Vivek Sanil

• Anonymous
  November 17, 2014
  Thank you. It is soo good to have background, why and what  you can do. I doubt I will ever need this but bad things happen. When talking to MicroSoft I do not want to look stupid but be as helpful as possible.

• Anonymous
  March 16, 2015
  If I'll delete the resource database what would be the impact.

• Anonymous
  September 25, 2015
  Thank you. It giving information about resource database

• Anonymous
  October 14, 2015
  Very good information  thanks a lots