How to Backup and Restore Resource Database in SQL Server

Microsoft SQL Server 2005 introduced a new system database namely Resource database. It’s a read-only system database which is hidden from users and in this article we will discuss how to backup and restore Resource Database in SQL Server. DBA should backup Resource Database along with other System Databases in SQL Server as part of Disaster Recovery and most importantly document the location of each and every system and user databases.

What is a Resource Database?

Resource database is a read-only system database which is hidden from users. System objects such as sys.objects are physically stored in Resource Database which appears logically in the SYS schema of each database. However, resource database will only store system objects and you cannot store user data or metadata. Resource database consists of two files namely mssqlsystemresource.mdf and mssqlsystemresource.ldf. Most importantly the ID of resource database is always 32767. The ID value of resource database has remained same across all versions of SQL Server 2005 to SQL Server 2014. For more information, see Resources Database in SQL Server.

What is the Importance of Resource Database?

Resource Database makes upgrading SQL Server to a New Version an easier and a faster procedure. In the previous versions of SQL Server, upgrading to a new version required dropping and creating system objects. However, since the resource database contains all system objects, an upgrade can now be achieved simply copying the resource database (mssqlsystemresource.mdf and mssqlsystemresource.ldf) files to the local server.

Where can I find Resource Database?

To Identify the Location of Resource Database in SQL Server execute the below TSQL query.

/* Identify the Location of Resource Database in 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

The physical file names of Resource database are mssqlsystemresource.mdf & mssqlsystemresource.ldf. Every instance of SQL Server has one and only one associated Resource Database related .MDF and .LDF files and the files are not shared between instances.

DBA must document the location of Resource and Master Database for each server which they maintain along with location of other System and User Databases.

Important Note: MSDN mentions that the Resource database depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location. For more information, see Physical Properties of Master.

Default Location of Resource Database in Default Instance of SQL Server is mentioned below for your reference:-

• SQL Server 2014: <drive>:\Program Files\Microsoft SQL Server\MSSQL12.<instance_name>\MSSQL\Binn\
• SQL Server 2012: <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\
• SQL 2008 R2:<drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\
• SQL Server 2008: <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\
• SQL Server 2005: <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\

How to Backup Resource Database in SQL Server?

SQL Server cannot backup Resource Database hence DBA will have to perform file-based or disk-based backup by considering mssqlsystemresource.mdf and mssqlsystemresource.ldf files as if they are .EXE files. Using the XCOPY, ROBOCOPY or COPY command you can copy the .MDF and .LDF files even when SQL Server is up and running. 

Script to Copy Resource Database Files Using XCOPY Command

XCOPY "D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn\mssqlsystemresource.mdf" "D:\DatabaseBackups\" /Y

XCOPY "D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn\mssqlsystemresource.ldf" "D:\DatabaseBackups\" /Y

SQL Server Agent Job Step to Copy Resource Database Files Using XCOPY command

XCOPY "D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn\mssqlsystemresource.*" "D:\DatabaseBackups\" /Y

How to Restore Resource Database in SQL Server?

Restoring Resource Database means copying mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the respective location which you have documented within your Disaster Recovery Plan.

Moreover:-

Resource Database should be present is the same location where master database files reside.

In case if there is a hardware failure and you need to rebuild your environment by restoring master database on to a new drive location. Then before restoring master database using WITH MOVE option a copy of Resource Database’s .mdf and.ldf files should be present.

If you could manage to find an older version of Resource Database they you will have to reapply the subsequent patches.