By: Daniel Farina | Updated: 2019-10-25 | 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.
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.
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:
- Security Update for SQL Server 2017 RTM (KB4505224)
- Security Update for SQL Server 2016 SP1 (KB4505219)
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.
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.
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
- In case you face this or a similar situation of needing to restore the resource database without having a backup, in order to be able to extract the mssqlsystemresource database files from a setup package you need to know the exact version of SQL Server you are running. If you don’t know how to find it, you can read this tip that explains various methods to find out: How to tell what SQL Server version you are running.
- It is a good practice to take backups of the resource database; if you want to know how to do it I suggest that you take a look at this tip: SQL Server backup and restore of the Resource database.
- In this tip I mentioned that you need a "Dedicated Admin Connections" in order to be able to access the resource database. You can read more about Dedicated Admin Connections on the SQL Server Dedicated Administrator Connection Tips Category and take the next tip as a starting point: Dedicated Administrator Connection in SQL Server.
- Additionally I mentioned that if you want to access the resource database, you also need to start SQL Server in single user mode. In this tip you will learn how to use the startup parameters that will allow you to start your instance in single user mode: Setting SQL Server Startup Parameters.
- If your problem includes a scenario of restoring the Master database without having a backup you can take a look at this tip: Restoring the SQL Server Master Database Even Without a Backup.
About the author
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
Article Last Updated: 2019-10-25
