Resource database for SQL Server

In this article we will discuss about SQL Server resource database.  

Resource DB is a system database. It is a read-only/hidden DB that contains all the system objects that are included with SQL Server. SQL server system objects (sys.objects) are physically persisted on resource DB but they logically appear in the sys schema of each database.

• Resource database is introduced from SQL server 2005 version.
• SQL Server cannot back up the Resource database
• Resource database does not contain user data or user metadata.
• Resource DB ID is always 32767.
• Name of Resource DB is “mssqlsystemresource”.

Physical location of Resource DB:

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located at <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\.

 

Note: Each instance of SQL server will have only one mssqlsystemresource.mdf file and will not share this file.

Purpose of Resource DB:

Using resource DB, upgrading the SQL server version will be easier and faster. In earlier version of SQL server while upgrading the versions it requires dropping and creating system objects. But now with the use of resource DB, we can do upgrade of SQL server versions easily by copying the single resource DB file to server machine.

Backing up and restoring the Resource DB:

SQL Server cannot back up the Resource database so we have to do manually. To back up this database stop the SQL server copy the physical files to another location of disk then start the service again.

To restore the Resource DB, create a new database and attach the files with below query.

sp_attach_db 'd:\mssqlsystemresource.mdf','d:\mssqlsystemresource.ldf'

Caution: you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version of resource DB backup.

The other important parameters which are associated with Resource database are

1. Version number of the Resource database – to obtain this value use SELECT SERVERPROPERTY('ResourceVersion');
2. Last updated date of Resource database – to get this value use SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');