Problem
For many people familiar with SQL Server, system databases are associated only with four databases – master, model, msdb, tempdb. Even in SQL Server Management Studio (SSMS), when we open the system database, we can see only these four databases (we are talking about a classic configuration, where some other features are not configured, replication, for example). However, besides these databases, there is one more, a fifth system database called the resource database. We are going to discuss this less familiar system database in this article and cover five important facts about this database.
Solution
As mentioned, when we open “System Databases” in the Object Explorer in SSMS, we can see only four databases (master, model, msdb, tempdb):
We can see the resource database does not appear. What is this “hidden” database and what it is used for? Well, the resource database is a system database and it makes the process of upgrading SQL Server to a newer version easier and faster. Now, let’s explore some facts about the resource database.
Fact 1 – The resource database is a read-only database and contains all system objects
Actually, all system objects that are logically in the sys schema of every database, are physically located in the resource database. For example, in SSMS, if we expand “System Views” under “Views” of any database, we can see many views in the sys schema:
The data in these views are stored in the resource database and in fact, if we read data from one of these views, we read 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]
So, the information about the system objects retrieved comes from the resource database:
However, if we try to access the resource database directly, we will receive an error:
USE [resource]
GO
SELECT 1
As we can see, when we use the resource database in the “USE <DATABASE>” statement, we get the message that that database does not exist:
Additionally, we can only read and not change the data of the resource database (read-only database). It is important to mention that the resource database does not store user data, user metadata or instance-related data.
Fact 2 – The resource database facilitates upgrading SQL Server to a newer version
In older versions of SQL Server, the Resource database is needed to drop and recreate the system objects while upgrading. However, in the newer versions, thanks to the resource database, the system objects can be transferred by copying the resource database file (as it contains all these objects). In SQL Server 2000 and earlier versions, there was no resource database. It is introduced in SQL Server 2005 and, therefore, makes upgrading easier and faster.
Fact 3 – The ID of the resource database is 32767
Each database in a SQL Server instance has an ID. The maximum number of databases that one instance can hold is 32767. This number is also the resource database ID and it is constant for any resource database in any instance. So, actually, the resource database is assigned with the maximum possible database ID in the instance. Having said that, the resource database and, therefore, its ID is not visible through the sys.databases table:
SELECT *
FROM sys.databases
In the result, we can see that all system and user databases with their IDs are listed, but not the resource database:
The information about the resource database is also unavailable while using DB_ID() and DB_NAME() functions:
--System databases
SELECT DB_ID( 'master' ) AS 'master db DBID'
SELECT DB_ID( 'tempdb' ) AS 'tempdb db DBID'
SELECT DB_ID( 'model' ) AS 'model db DBID'
SELECT DB_ID( 'msdb' ) AS 'msdb db DBID'
SELECT DB_NAME(1) AS 'master'
SELECT DB_NAME(2) AS 'tempdb'
SELECT DB_NAME(3) AS 'model'
SELECT DB_NAME(4) AS 'msdb'
--User database
SELECT DB_ID( 'newdb' ) AS 'newdb db DBID'
SELECT DB_NAME(5) AS 'newdb'
With this query, we get the system and user databases ID by their names and vice versa:
The same functions, however, do not return any information about the resource database:
--Resource database
SELECT DB_ID( 'resource' ) AS 'resource db DBID'
SELECT DB_NAME(32767) AS 'resource'
NULL is returned in both cases:
Nevertheless, it is still possible to query some information about the resource database, for instance, with these queries below:
SELECT SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion'
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime'
The result shows the version of the resource database and the last time the resource database was updated:
Fact 4 – Moving the resource database files is not supported
The resource database has one data and one log file called mssqlsystemresource.mdf and mssqlsystemresource.ldf correspondingly.
The location of these files is “<drive letter>:\Program Files\Microsoft SQL Server\MSSQL<version num>.<instance_name>\MSSQL\Binn\”. In our example, this location is “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\”, so we can find these files there:
The location of the resource database’s files can be retrieved by this query:
SELECT *
FROM sys.sysaltfiles
WHERE dbid = 32767
Where 32767 is the resource database id, as we have already discussed:
While there are special procedures for relocating other system databases’ files, changing the location of the resource database files is neither supported nor recommended. Upgrading SQL Server sometimes can cause a new resource database to be installed and always uses 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
We cannot perform traditional backup and restore methods to backup or restore the resource database. In order to backup this database, we can take backup copies of the database files. It is worth mentioning that although the resource database’s data file extension is .mdf, we should consider it as an .exe file. The backup files can be used to restore the resource database by copying them to the corresponding location.
Conclusion
Thus, there are five system databases and not four as many people think. The resource database is the fifth, “hidden” database that is a read-only database and contains all system objects of SQL Server. It is used to make the upgrade process of SQL Server faster and easier. This database cannot be backed up and restored (using traditional SQL Server methods) and also, the location of its data and log files cannot be changed. It has a fixed database ID of 32767, which is the maximum number of databases per instance.
Next Steps
For more information, please follow the links below:
