Restoring MSDB Database

One of the primary reasons to take backups is to protect from accidentally data deletion and data corruption.

But, what happens when a SQL Server job is accidentally deleted? MSDB database contains all the information about backups,SQL Server Agent Jobs,Alerts,Operators..etc. So, the deleted job can be recovered by restoring MSDB database.

To restore the MSDB database, the server does not need to be in Single User Mode.To restore Master database, the server needs to be in single user mode and I explained it in this blog.

So, to restore MSDB, make sure you can get exclusive access on the database.Remember, MSDB is a system DB and is inherently used for many purposes such as maintaining job history,job step logs,notifications,database mail, alerts..etc. So, it may be better to stop SQL Server Agent while restoring MSDB database.To restore MSDB database:

Use Master
Restore Database MSDB from disk='E:\SQL_Backup\Msdb.bak' with replace

One other alternative is to set the MSDB database in read only mode(so as, no new data is written but applications can continue to read data) and restore MSDB database as user database and then stop the SQL Server Service account and copy data and log files to where MSDB files are located and rename them as MSDB files and restart the SQL Service. This might not be possible in all cases as it involves restarting the SQL service.