When it comes to SQL Server, most people are familiar with the four system databases – master, model, msdb, and tempdb. However, there is a fifth system database called the resource database that often goes unnoticed. In this article, we will delve into the resource database and uncover five important facts about it.

Fact 1 – The resource database is a read-only database and contains all system objects

The resource database is a hidden database that is not visible in SQL Server Management Studio (SSMS). It is a read-only database that stores all the system objects, such as views, in the sys schema. These system objects are physically located in the resource database, and when we access them from other databases, we are actually reading from the resource database.

SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published]
FROM [NewDB].[sys].[objects]

However, directly accessing the resource database will result in an error, as it is a read-only database that does not store user data, user metadata, or instance-related data.

Fact 2 – The resource database facilitates upgrading SQL Server to a newer version

The resource database plays a crucial role in upgrading SQL Server to a newer version. In older versions, system objects needed to be dropped and recreated during the upgrade process. However, thanks to the resource database, the system objects can now be transferred by simply copying the resource database file. This makes the upgrading process faster and easier.

Fact 3 – The ID of the resource database is 32767

Each database in a SQL Server instance has a unique ID. The resource database is assigned the maximum possible database ID, which is 32767. However, this ID is not visible through the sys.databases table or the DB_ID() and DB_NAME() functions. Despite this, it is still possible to query some information about the resource database using the SERVERPROPERTY() function.

SELECT SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion'
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime'

Fact 4 – Moving the resource database files is not supported

The resource database has fixed data and log files called mssqlsystemresource.mdf and mssqlsystemresource.ldf, respectively. These files are located in the “Program Files\Microsoft SQL Server\MSSQL\.\MSSQL\Binn\” directory. Unlike other system databases, the location of the resource database files cannot be changed. Upgrading SQL Server may result in a new resource database being installed, and it will always use this fixed location for storing its files.

Fact 5 – It is not possible to backup and restore the resource database using a SQL Server backup

Traditional backup and restore methods cannot be used to backup or restore the resource database. Instead, backup copies of the database files should be taken. Although the resource database’s data file has the .mdf extension, it should be treated as an .exe file. To restore the resource database, the backup files can be copied to the corresponding location.

Conclusion

The resource database is a hidden, read-only database in SQL Server that contains all the system objects. It plays a crucial role in the upgrading process, facilitates the transfer of system objects, and makes the upgrade faster and easier. However, it cannot be backed up or restored using traditional SQL Server methods, and the location of its files cannot be changed. Understanding the resource database and its functionalities is essential for SQL Server administrators and developers.

Article Last Updated: 2021-04-06