Overview
A common question about the MSDB databases is whether you need to backup the database, and if so, how.
Explanation
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 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 in 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.
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
