SQL Server DBA Tips and Tricks

Till now you are aware about the 4 main system databases. However, there is one more important, but hidden system database is exists. Yes, it is called as Resource Database or mssqlsystemesource database.

Today, we will explore this hidden database in SQL Server.

• This is hidden and read only system database.
• It does not contain user data or user metadata.
• It contains all the system objects that are included with SQL Server.
• SQL Server system objects like sys.objects are resides in this database, however, they logically appear in the sys schema of all the databases

Physical Properties of Resource Database

• Below are the physical file names of the resource database
  • mssqlsystemresource.mdf
  • mssqlsystemresource.ldf
• Below is the location of these files.
  <drive>;\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\
• Each instance has only one associated mssqlsystemresource.mdf file.
• Changing the location of resource database files is not at all recommended.
• The Id of this Database is always 32767.
• Other important values associated with this database are as below
  • Version Number
    SELECT SERVERPROPERTY('ResourceVersion');
GO
  • Last time the database was updated
    SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO

Backup and Restore of Resource Database

• We can not backup this database using SQL server like other system or user databases.
• If you want to backup, you can take the backup of the resource database files directly like file backup.

Please check below Microsoft article for further information.

Resource Database

Check all related articles