Recently, I received an interesting question from one of my readers, Joseph Kazeka. He discovered a database called “mssqlsystemresource” while exploring his computer’s directory. In this article, we will explore what the mssqlsystemresource database is and its significance in SQL Server.

The mssqlsystemresource database is actually the Resource Database in SQL Server. It is a read-only database that contains system objects such as sys.objects, sys.modules, and other sys schema objects. Unlike regular user databases, the resource database does not store any user data.

The primary purpose of the resource database is to facilitate the upgrading process when moving to a new version of SQL Server. In previous versions, when upgrading SQL Server, all the system objects from the previous version had to be dropped and new version system objects had to be created. However, with the introduction of the resource database, the upgrade process became much smoother.

When upgrading SQL Server to a new version, the resource database can be simply replaced at the file system level using the operating system. This means that if the new version of SQL Server is causing any issues, rolling back to the previous version is as simple as replacing the resource database with the previous version.

It is important to note that each SQL Server instance has only one resource file, and its location should be the same as the master database. The resource database is hidden from users and cannot be viewed in the Object Explorer or using the sp_helpDb stored procedure. Additionally, SQL Server itself cannot back up the resource database, so it must be backed up using a file system backup.

One interesting fact about the resource database is its fixed ID, which is 32767. This ID remains constant across all SQL Server instances.

To see some of the properties of the resource database, you can run the following T-SQL script:

SELECT SERVERPROPERTY('ResourceVersion') AS [Resource Version];
GO
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime') AS [Resource Last Update DateTime];
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects')) AS [SQL Definitions];
GO

By executing this script, you can retrieve information about the resource version, the last update date and time, and even view the SQL definitions of the system objects within the resource database.

In conclusion, the mssqlsystemresource database, also known as the Resource Database, is a crucial component of SQL Server. It contains system objects and allows for seamless upgrades to new versions of SQL Server. Understanding its purpose and properties can help database administrators effectively manage and maintain their SQL Server environments.