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:
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.
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:
--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 the resource database on an instance of SQL Server, a query is as follows:
-- 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, a query is as follows:
-- 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!
Comments