Recover SQL Server Resource Database

By:   |   Comments (2)   |   Related: > SQL Server Configurations


Problem

A patch was applied automatically to my instance of SQL Server by Windows Update. This could have gone unnoticed, as it often happens with Windows updates.  When I tried to access my SQL Server instance, I found that the mssqlsystemresource database was absent from where it should be and SQL Server wouldn't start up. In this tip I will tell you how I solved this issue.

Solution

The other day I attempted to connect to my SQL Server test environment with SQL Server Management Studio and got the "server not found" error message.

As a normal reaction, I opened SQL Server Configuration Manager to check the SQL Server service status; and as you may guess, the service was not running. At this point I was a bit worried, because the services for SQL Server are set to start automatically.

I preceded with the first step of recovery, which was to try to start the instance and see what happens.  As I tried to start the SQL Server instance, I got the following error message saying that the request failed and that I have to consult the event log or other applicable error log for details. You can see the message on the screen capture below.

SQL Server fails to start.

At this point I realized that it wasn’t an easy thing to solve, so I followed the message’s instructions and opened the Event Viewer application to view the event log in search for an answer.

First, I looked at the Application log and searched for an event with a source equal to MSSQLSERVER which is the instance of SQL Server I was troubleshooting. I found that there was an error related to SQL Server that said "Open Failed: Could not open file mssqlsystemresource.mdf OS error: 2" (the system couldn't find file specified) as is shown on the screen capture below. For those of you who don’t know, the file mentioned below is part of what is known as the Resource Database and is required for SQL Server to start up.

Event Log shows that file mssqlsystemresource.mdf is not found.

The SQL Server mssqlsystemresource Database

The mssqlsystemresource database is the physical database which contains all the definitions of the system objects included in SQL Server.  This database is read only and is not accessible even for members of the sysadmin server role unless the instance is accessed via a dedicated administrator connection after being started in single user mode (with the –m startup option).

Due to the fact that the Resource database contains all of the system objects code (it does not contain any user data or metadata), the upgrade of versions is performed by simply replacing the Resource database files on the instance’s BINN folder.

Recovering the SQL Server mssqlsystemresource database

After being finding out that the files for the mssqlsystemresource database were missing, I needed to know why it happened and how to fix the issue. The first thing I instinctively did was to search on the internet about mssqlsystemresource databases disappearing and I found the following Microsoft article: FIX: 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.

Being aware that the Microsoft article stated that the affected versions are SQL Server 2012 and 2014, meanwhile my system was SQL Server 2017, but I thought I would go down this path to see.

 To my surprise I saw that the following two updates were scheduled to run one after another:

Update history shows two SQL Server updates one after another.

Now that I knew the cause, I needed to find out how to restore the mssqlsystemresource database, since it is my home computer, I didn’t have a backup. I tried downloading the package and installing it manually, but maybe because the mssqlsystemresource database was missing, it didn’t work; something of a chicken-and-egg situation.

It wasn’t my intentions to reinstall SQL Server, so I opted for a more heterodox approach. I opened the temporary folder that the patch uses to decompress its files. You can make a note of that folder after you launch the setup program by taking a screen capture as I did next.

Setup extracting installation files.

After locating the temp directory, I searched for the path x64\setup\ and after looking at the files I guessed that sql_engine_core_inst.msp may have a copy of the mssqlsystemresource database. This file is compressed, so in order to open it you need an uncompressing tool. I used 7-Zip which is free and you can download from https://www.7-zip.org/.

On the next screen capture you can see that there are two files, ENG_RE_ResourceDb_ldf_64 and ENG_RE_ResourceDb_mdf_64. These are the files of the mssqlsystemresource database for the 64 bit edition of SQL Server in the English language. If you need another language, you have to change the language on the patch download page. Remember that the setup engine contains all the languages, but the deployment packages are in a specific language.

The compressed file sql_engine_core_inst.msp contains the resource database files.

I extracted the two files and then renamed them as follows and copied these to the folder mentioned above in the error message where these files should exist.

  • ENG_RE_ResourceDb_ldf_64 renamed to mssqlsystemresource.ldf
  • ENG_RE_ResourceDb_mdf_64 renamed to mssqlsystemresource.mdf

After these files were renamed and copied to the correct folder, I was able to start the SQL Server services and everything worked again.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, November 22, 2019 - 3:07:41 AM - Wilfred van Dijk Back To Top (83163)

Thanks, handy resource in case I have the same issue!


Friday, October 25, 2019 - 7:02:39 PM - Ted Back To Top (82902)

Fascinating.  Excellent write-up, thank you Daniel.















get free sql tips
agree to terms