September 2008 | SansSQL

Thursday, September 25, 2008

SQL Server 2005's 5th system database - MSSQLSystemResource (The Invisible Database)

MSSQLSystemResource is a database that complements the master db. It is like the name smartly impels a resource database.
All system stored procedures, views and functions are stored here.

This database is hidden from the user. We can't view it in Object Explorer or with the use of sp_helpDB or by selecting from a sys.databases view. Resource database does not contain any of user data. This database has to be backed up using file-based backup or by using Drive Backups.

So how do we know its presence?
-Go to the Data directory of your SQL installation [Install Drive]:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and there you will find the MSSQLSystemResource.mdf and MSSQLSystemResource.ldf.

If you want to see what is there in this database:
  1. Stop the SQL Server service.
  2. Copy both files and rename the copies to MSSQLSystemResource_Test.mdf and MSSQLSystemResource_Test.ldf.
  3. Start the SQL server service
  4. Attach the new files.
  5. Query the database.