SQL SERVER – mssqlsystemresource – Resource Database

Just a day ago I received following email

“Dear Pinal,

While I was exploring my computer in directory C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data I have found database mssqlsystemresource. What is mssqlsystemresource?

Thanks,

Joseph Kazeka”

Simple question like this are very interesting. mssqlsystemresource is Resource Database. It is read only database and contains system objects (i.e. sys.objects, sys.modules and other sys schema objects). Resource database does not contain any of user data.

The purpose of resource database is to facilitates upgrading to new version of SQL Server without any hassle. In previous versions whenever version of SQL Server was upgraded all the previous version system objects needs to be dropped and new version system objects to be created. With additional of resource database when SQL Server is to be upgraded to new version resource database has to be simple replaced as file system level (using OS). If new version is creating problems rolling back can be done very easily, just replace resource database with previous version.

For each SQL instance there is only one resource file. Resource file location should be same as master database. This databse is hidden from the user. It can not be viewed in Object Explorer or with the use of sp_helpDb or any other way. SQL Server itself can not backup this database. This database has to be backed up using filesystem backup.

Another interesting fact about Resource Database is its fixed ID 32767. Run following T-SQL script to see some of the properties of the Resource Database.

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

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