A hidden fifth System database in SQL Server: The Resource Database

Apart from the four system databases, we already knew, Microsoft has introduced a new database called mssqlsystemresource from SQL Server 2005, which is often referred to as the resource database. This is the database where the definitions of all programmable system objects are stored, such as your system stored procedures, system functions and all the definitions of the new metadata views. This includes the compatibility views such as sysdatabases and sysobjects, as well as the directory views like sys.databases and sys.partitions. For all practical purposes, this database is read-only and hidden for users.

What is the purpose of introducing a new system database?

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

This Resource database expedites the upgrade process and makes the task easy. Since all the system objects reside under resource database, during the upgrade we can just over write the resource database files with the new resource database files which will update all the system objects existing in the database. In short, a new resource database is simply copied and replaces the original resource database.

Where is it located?

The location of the resource database differs from versions of SQL Server. The following script can help you identify the data file & log file location.

As you can see from the script below, the database id for resource database is always 32767 irrespective of the version and edition of SQL Server.

use master

GO

SELECT 'ResourceDB' AS 'Database Name'

, NAME AS [Database File]

, FILENAME AS [Database File Location]

FROM sys.sysaltfiles

WHERE DBID = 32767

GO

Hope you enjoyed reading this article 🙂