SQL Server MSDB Database Disaster Recovery


As with any SQL Server database, you should also have a disaster recovery plan for MSDB.

Should you script out objects from the MSDB database for disaster recovery?

MSDB would be better protected with a recent backup.  One could attempt to script out every SQL Server Agent Job, operator, alert, etc, but it would be very hard to keep them up to date.  Such a script would also fail to include things like the SQL Server Agent history.  A full backup of MSDB would be the much easier way to include all objects and history stored by MSDB.

Can you copy and replace the MSDB database files from one instance to another?

A backup of MSDB can be restored to the same or to a newer version of SQL Server.  The SQL Server Agent will pick up and start the execution of the jobs.  It will attempt log shipping on existing databases.

Remember that any encrypted data (such as passwords for DBMail) will not load unless the database master key is restored as the current key is encrypted by the Service Master Key which would be different on another instance.

Comments For This Article

get free sql tips
agree to terms