;

The Resource Database: The Fifth Hidden System Database of SQL Server


Tutorialsrack 02/10/2019 SQL Server

In this article, we will know about the fifth hidden system database of SQL Server: The Resource Database, What is Resource Database, what is the purpose of introducing the new database, where it is located, what is the version number of resource Database and when was the last time resource database updated.

We already knew that SQL Server has 4 visible system databases such as 'master', 'model', 'msdb', 'tempdb'. In SQL Server 2005, Microsoft has introduced a new database is called mssqlsystemresource, which is also referred to as Resource Database. This is the database that contains all the system objects such as sys.objects, etc. These objects physically exist in the Resource Database but logically appear in the SYS schema of every database on the instance of SQL Server. For all practical purposes, this database is read-only and hidden for users.

Here are some basic facts about this database:

  • This database is a read-only and hidden for users which can only be seen via DAC (Dedicated Admin Connection) When SQL is running in a single user mode.
  • This database contains only the definition of system objects such as system database, system tables/views/procedures and so on.
  • You can treat this as one of the binary files (like DLL or EXE).
  • The database ID for this database is always 32767. The ID value of the resource database has remained the same across all versions of SQL Server 2005 and later.

What is the Purpose of Introducing a New System Database?

Before MS SQL Server 2005 was introduced, if you want to upgrade a SQL Server, then all system objects must be deleted and restored at the time of upgrade, which is time-consuming and the installation will fail if any one of the system objects causes some problems. To resolve this issue, the resource database was introduced.

This Resource database speeds up the upgrade process and makes the task easy. Since all of the system objects resides under the resource database, during the time of upgrade, we can just overwrite the resource database files with the new resource database files which will update all the system objects that are already exists in the database.

Identify the Location of Resource Database in SQL Server

The location of the resource database differs from the versions of SQL Server. Most importantly, the database ID for this database is always 32767. The ID value of the resource database has remained the same across all versions of SQL Server 2005 and later.

To identify the location of the Resource Database, a query is as follows:

Example - To identify the location of the Resource Database
--Identify the location of Resource Database

Use master;

SELECT 'ResourceDB' AS 'Database Name',
NAME AS [Database File], FILENAME AS [Database File Location] 
FROM sys.sysaltfiles 
WHERE DBID = 32767;

To Check the Current Version of Resource Database on an Instance of SQL Server

To check the current version of the resource database on an instance of SQL Server, a query is as follows:

Example - To check the current version of the resource database on an instance of SQL Server
-- Version Number of Resource Database

SELECT SERVERPROPERTY ('ResourceVersion') AS 'Resource DB Version Number';

This version number is the same as the build number of SQL Server.

To Check When was the Last Time Resource Database Updated

To check when was the last time resource database updated, a query is as follows:

Example - To check when was the last time resource database updated
-- To check When was the Last Time Resource Database updated 

SELECT SERVERPROPERTY ('ResourceLastUpdateDateTime') AS 'Resource Database Last Updated on';

I hope this article will help you to understand the fifth hidden system database of SQL Server: The Resource Database. 

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!


Related Posts



Comments

Recent Posts
Tags