Share via

The Hidden Database in SQL Server 2005 and 2008

  • Article

Have you ever had to move your master database? Or have you ever opened the folder which contains your master database? Both cases - if that ever happened to you, you should already know about the Resource Database - a hidden, read-only database introduced with SQL 2005 which contains all the system objects that are included with SQL Server.

 

The purpose of this database it to speed up the upgrading process. 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. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the Resource database with the older version.

 

What you need to know about it:

 

  • The Resource Database must be placed in the same location as the master database. Therefore - in SQL 2005 and 2008 - if you move the master database you need to move the resource database as well otherwise your SQL Server installation won't start.

More info: https://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

 

  • You should back it up at each upgrade of your installation (SP, CU or hotfix). Unfortunately, as it's not a standard database, you cannot back it up in the traditional way, but you have to perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file.

 

Note that - After restoring a backup of the Resource Database you must reapply any subsequent updates. Say that you took your RD backup when you upgraded to CU1, now you are on CU10 and have lost the RD - you can re-apply the old backup but then  you will have to re-apply all the update steps you're performed since CU1.

 

To determine the version number of the Resource database, use:

SELECT SERVERPROPERTY('ResourceVersion');
GO

 

More info:

 

Resource Database

https://msdn.microsoft.com/en-us/library/ms190940(SQL.90).aspx

 

Reserved Resource Database in SQL Server 2005

https://www.extremeexperts.com/sql/Yukon/ResourceDatabase.aspx

 

SQL Server backup and restore of the Resource database

https://www.mssqltips.com/tip.asp?tip=1544

 

Geek City: The Resource Database

https://sqlblog.com/blogs/kalen_delaney/archive/2007/09/13/geek-city-the-resource-database.aspx

 

- Beatrice Nicolini -