SQL Server MSDB Database Restore



By:
Overview

In this section we cover whether you should restore the MSDB database and how.

Is there ever a need to restore the MSDB database?

The MSDB database would need to be restored in the event 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.


Last Update: 3/31/2020






Comments For This Article





download








get free sql tips
agree to terms


Learn more about SQL Server tools