SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

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.

DBAs 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
TSQL Query to Find Resource Database Location in SQL Server

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.

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 XCOPYROBOCOPY 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
Backup Resource Database MDF and LDF files using XCOPY

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
SQL Server Agent Job Step to Copy Resource Database Files Using XCOPY command

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.

Next Steps

Ashish Mehta

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,500+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips