SQL SERVER – How to View Objects in mssqlsystemresource System Database?

The series of interview questions are always fun. Sometimes I get to hear strange questions and are worth a detailed post. This blog is in continuation to one such question that landed into my Inbox which I personally felt had to have little explanation. During interviews there would be at least one question asked about system databases and someone might ask about hidden system database is – mssqlsystemresource. There are a few facts which are known to most of us:

1. It is a hidden system database. ID of this database is 32768.
2. It stores schema of system object.
3. It helps in faster patching because there is no need to ALTER system objects. Since the schema is stored in this database, it would be just replacing mdf and ldf files.

There are a few mythsmyths about this database, none of below is true.

1. It can be used to rollback service pack. Just replace old files and you are done. This is INCORRECT! Service pack is not just this database replacement.
2. Backup and restore needed for this database. This is also INCORRECT! This database is just like any other binary like exe and DLL which are needed for SQL Server to run. In case the files are damaged, you need same version of the file from some other SQL instance.

Here are few blogs which I have written earlier:

SQL SERVER – mssqlsystemresource – Resource Database

SQL SERVER – Location of Resource Database in SQL Server Editions

If you want to see the objects under this database, there is a little trick.

• Stop the SQL Server service
• Copy the mssqlsystemresource.mdf and mssqlsystemresource.ldf to a new path. Location of these files are listed in my earlier blog listed above,
• Start the SQL Server Service.
• Use the following command to attach the data and log file as a new user database.

USE [master] GO
CREATE DATABASE [mssqlsystemresource_copy] ON
(FILENAME = N'E:\temp\mssqlsystemresource.mdf' ),
(FILENAME = N'E:\temp\mssqlsystemresource.ldf' )
FOR ATTACH
GO


• Here is what we would see in management studio. Note that there are NO tables in this database. Just the views and procedures which are in sys schema. Generally they are visible under system views in other databases

We can also have a look at the definition of views. Note that this option won’t come for system objects.

In practical scenario, there is no need to ever do this but it is always good for a DBA to know what is happening under the hood in SQL Server. I hope this will give you more opportunity to explore.

Note: Please DONOT change the system ResourceDB or replace the same in production environments. It is not advisable. This blog has to be seen as educational and for exploration purposes only.

Reference: Pinal Dave (https://blog.sqlauthority.com)