Overview
In this section, we cover whether you should restore the MSDB database and how.
Explanation
Is there ever a need to restore the MSDB database?
The MSDB database would need to be restored in the event that it showed corruption during a consistency check. A restore might also be needed if an object, such as an Agent Job step, was improperly modified.
How to restore the MSDB database?
The first step to a restore of MSDB is to decide if the entire database needs to be restored or if an object or row simply needs to be recovered.
If the issue is corruption, then restoring an entire database is probably the best way to recover. If a restore is being considered because someone modified a SQL Server Agent job or deleted an operator, then an object-level recovery makes more sense.
To do object-level recovery, simply restore a recent MSDB backup to a new user database with a name like MSDB_Recover. Then, query the necessary table(s) to view the missing data and use that information to rebuild the broken object. A group of common tables to be queried appears earlier in this tutorial.
Instructions for a complete database restore appeared earlier in this tutorial under the section about how to move MSDB.
Eric has been a SQL Server DBA and Architect in the legal, software, transportation, and insurance industries for over 10 years. Currently he is the Sr Data Architect for Squire Patton Boggs, a leading provider of legal services with 47 offices in 20 countries.
Eric is a 2018-2019 Idera Ace and has co-authored 2 Idera Whitepapers.
He has been a presenter at PASS Summit, IT/DevConnections, SQLSaturdays, the in.sight transportation conference, and the Ohio North SQL Server User’s Group.
- MSSQLTips Awards: Author of the Year Contender – 2021, 2022 | Trendsetter (25+ tips) – 2021
