Backup and Restore Resource Database in SQL Server
Nov 14, 2014
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.
SQL Server Database Backup Options and Commands a Step by Step Tutorial with Examples
Why SQL Server Log backups fails and Full backup succeeds? Learn how to Fix BACKUP detected corruption in the database log error in SQL Server
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
'ResourceDB' AS 'Database Name'
, NAME AS [Database File]
, FILENAME AS [Database File Location]
WHERE DBID = 32767
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
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.
- 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.
- It is highly recommended to document the location of Resource and Master Database along with other System and User Databases.
- Create an SQL Server Agent Job to backup Resource Database once a day or at least immediately after patching the server.
- Read Resources Database in SQL Server to See when last time Resource Database was updated, Identify the current version of Resource Database.
MORE SQL SERVER PRODUCT REVIEWS & SQL SERVER NEWS