Based on Microsoft Documentation <The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata>

In this article, I will explain below 5 points:

  • information you need to know it about resource Database
  • List of Errors You can See it when mssqlsystemresource Corrupted or lost
  • Methods for troubleshooting these errors
  • Other Solutions to repair ‘mssqlsystemresource’ Database
  • How to take a backup from mssqlsystemresource Using SQL Server T-SQL

Other information you need to know it about resource Database

  • Resource Database is one of the SQL Server system Databases but we can’t see it use SQL Server management studio
  • It is a read-only database that contains all the system objects that are included with SQL Server
  • The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf.
  • This Files of Resource DB are located in the SQL Server default installation path (<drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\)
  • 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 by copying the resource database (mssqlsystemresource.mdf and mssqlsystemresource.ldf) files to the local server
  • This Files should not be moved
  • Changing the location of the resource database is not supported or recommended
  • 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 system and user database.
  • You can take a copy from Database files using XCOPY, ROBOCOPY, or COPY command you can copy the.MDF and.LDF files even when SQL Server is up and running.
  • Restoring Resource Database means copying mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the respective location which you have documented
  • In case if there is a hardware failure and you need to rebuild your environment by restoring the master database onto a new drive location. Then before restoring a master database using WITH MOVE option a copy of Resource Database’s .mdf and.ldf files should be present

List of Errors You can See it when mssqlsystemresource Corrupted or lost

  • Error: 17207, Severity: 16, State: 1. FCB::Open: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘C:\ Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL \DATA\mssqlsystemresource.mdf’. Diagnose and correct the operating system error, and retry the operation
  • Error: 17204, Severity: 16, State: 1. FCB:: Open failed: Could not open file C:\ Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL \DATA\mssqlsystemresource.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
  • Error: 5120, Severity: 16, State: 101. Unable to open the physical file “C:\ Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL \DATA\mssqlsystemresource.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.
  • Error: 945, Severity: 14, State: 2. Database ‘mssqlsystemresource’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details
  • Error: 824, Severity: 24, State: 2.SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:43686; actual 0:0). It occurred during a read of page (1:43686) in database ID 23 at offset 0x0000001554c000 in file
  • The Resource database is missing after you install updates or service packs for instances of SQL Server 2012 one after another and then restart the server

Methods for troubleshooting these errors

  1. Review the complete path from the error message.
  2. Ensure the disk drive and the folder path is visible and accessible from Windows Explorer.
  3. Review the Windows Event log to find out if any problems exist with this disk drive.
  4. Fix Error MSSQLSERVER_17207 https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-17207-database-engine-error?view=sql-server-ver15
  5. Fix Error MSSQLSERVER_17204 https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-17204-database-engine-error?view=sql-server-ver15
  6. Fix Error MSSQLSERVER_5120 https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-5120-database-engine-error?view=sql-server-ver15
  7. Fix Error MSSQLSERVER_945 https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-945-database-engine-error?view=sql-server-ver15
  8. Fix Error MSSQLSERVER_824 https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-824-database-engine-error?view=sql-server-ver15
  9. Fix Resource Database is Missing https://support.microsoft.com/en-hk/help/3074535/kb3074535-fix-the-resource-database-is-missing-after-you-install-updat

Other Solutions to repair ‘mssqlsystemresource’ Database

When the Resource database is damaged or missing there are two possible alternatives. Either it can be repaired using the SQL Server setup or the database files can be restored from backup

  • First Solution: Repair the Resource Database using SQL Server Setup

SQL Server setup offers the option repair to repair a corrupted installation.
setup.exe /q /ACTION=Repair /INSTANCENAME=<instancename>
A repair does do more than re-creating the mssqlsystemresource database.
It checks all permissions, registry entries, module registration, …
Sometimes it happens, that a repair introduces subsequent issues

  • Second Solution: Restore the Resource Database from Backup or take it copy from another SQL Server

Restore Database Files (MDF) and (LDF) from Your Backup but In case you don’t have a backup for the mssqlsystemresource database at this time you can take a copy from the Database file from another SQL Server instance but you should make sure that this database matches your current SQL Server version (build number)

How to take a backup from mssqlsystemresource Using SQL Server T-SQL

In this section you can find one Stored Procedure take one parameter the Destination you need to save the backup of mssqlsystemresource on it and inside the Stord procedure I am doing enable for xp_cmdshell as it is required to be able to Execute XCOPY Command, then at the end of the SP, I am doing disable for xp_cmdshell, because based on Microsoft recommendation it should be disabled to limit the access for the windows server files from SQL Server. But in case you need to keep it enable you should do commit for the part of Disable the xp_cmdshell.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[Backup_ssqlsystemresourceDB]    Script Date: 10/20/2020 2:21:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Proc [dbo].[Backup_ssqlsystemresourceDB]
@DestinationBackupPath NVarchar(1000)
AS
begin
/************************************************************************************************************************************
Procedure:          dbo.Backup_ssqlsystemresourceDB
Create Date:        2020-10-20
Author:             Mustafa Elmasry 
Description:        take backup from Data File (MDF) and Log File (LDF) for ssqlsystemresourceDB
Parameter(s):       @DestinationBackupPath - Path of the Destination you need to save your backup on it
                    
Usage:              Exec [Backup_ssqlsystemresourceDB] 'D:\Work\'
****************************************************************************************************
Notes
To be able to Execute this SP you need to enable xp_cmdshell and based on Microsfot recommendation 
it is secuirty risk so i created the SP to enable the xp_cmdshell before start
then at the end the SP will diable again xp_cmdshell
So in case you don't need to disable xp_cmdshell you should remove the last part in the SP
IF you didn't enable the xp_cmdshell You will get error like this 

Msg 15281, Level 16, State 1, Procedure Master.dbo.xp_cmdshell, Line 1 [Batch Start Line 29]
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' 
because this component is turned off as part of the security configuration for this server. 
A system administrator can enable the use of 'xp_cmdshell' by using sp_configure.
For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

***************************************************************************************************/
-- Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
Declare @mssqlsystemresourceDB_FULLPath Nvarchar(1000)
Set @mssqlsystemresourceDB_FULLPath = (
SELECT 
FILENAME AS [Database File Location] 
FROM sys.sysaltfiles 
    WHERE DBID = 32767 and NAME = 'data')
Select @mssqlsystemresourceDB_FULLPath
Declare @mssqlsystemresourceDB_backupPath Nvarchar(1000)
Set @mssqlsystemresourceDB_backupPath = (select  substring(@mssqlsystemresourceDB_FULLPath,0,CHARINDEX('mssqlsystemresource.mdf',@mssqlsystemresourceDB_FULLPath))) 
Select @mssqlsystemresourceDB_backupPath
Declare @SQL varchar(1000)
Set @SQL = 'XCOPY' + ' "' + ''+@mssqlsystemresourceDB_backupPath+'' + 'mssqlsystemresource.*' + '"' + ' "' + ''+@DestinationBackupPath+'' + '" /Y'
print @SQL
Exec Master.dbo.xp_cmdshell  @SQL
-- Disable xp_cmdshell
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
End

Keep Following

Cloud Tech Website blog survey

IF you found this blog is helpful and sharing useful content please take few second to do rate the website blog from here

Azure Articles

IF you need to learn More about Azure SQL check this path more than 60 articles in Azure SQL

Published by Mustafa EL-Masry

I am Microsoft database consultant working as a Database administrator for more than +10 Years I have very good knowledge about Database Migration, Consolidation, Performance Tuning, Automation Using T-SQL, and PowerShell and so many other tasks I do it in multiple customers here in KSA and as of now, I am working in Bank Albilad managing the core banking system that is hosted in SQL Server Database 8 TB. Also, I am Microsoft certified 2008 and 2016 in SQL Server (2x MCTS, 2x MCTIP, MCSA, MCSE) and I am Microsoft Certified Trainer (MCT) also I am azure Certified (AZ-900, AZ-103) also I was awarded by Microsoft Azure Heroes 3 times as (Azure Content hero, Azure Community hero and Azure Mentor) For more information check my page https://mostafaelmasry.com/about-me/

2 thoughts on “Solve SQL Server Resource Database Crash”

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.