By: Eric Blinn
Overview
A common question about the MSDB databases is whether you need to backup the database and if so how.
Do I need to backup the MSDB database?
Yes, yes, yes! In the event that the MSDB database becomes corrupt or if the server crashes and the MSDB database needs to be recovered, a backup will be the only way to do so. This database also houses data that could be accidentally damaged by an administrative user. If such a person modifies or deletes a SQL Server Agent Job, step, or operator, an object can be recovered via a MSDB backup.
It is generally considered a best practice to backup the full MSDB database every day. In the event MSDB is exceptionally large on an instance, then differentials can be used.
How do I backup the MSDB database?
The MSDB database can be backed up the exact same way as any other database. The code below will back up the database to a local file. Consider using a location that is not local to the SQL Server -- if that is possible -- as that reduces the risk of losing the MSDB database in the case where the server becomes unavailable.
BACKUP DATABASE MSDB TO DISK='C:\MSDB.bak';
How frequently do I backup the MSDB database?
Best practices suggest backing it up every day. Its size generally allows for that.
