Great question!
Before I answer this right off, ask yourself these questions: “Do I want to lose access to all other databases on the instance? What would happen if I lose the model my company demands for the specific way every new database must be created? Would anyone notice if I had to restore after a disaster and no one had correct passwords to the database?” That shiver that just ran up your spine is your answer. Absolutely YES, the system databases (Master, MSDB, and Model) should have backups!
Master
It is recommended that the Master be backed up as often as necessary to protect the data: a weekly backup with additional backups after substantial updates is highly recommended. If for some reason the Master becomes unusable, restoring from the backup is the best way to get up and running. Go here for information on Restoring the Master. If you do not have a valid backup of the Master, rebuilding the Master is the only option. You can click here to find more information about what it takes to Rebuild System Databases.
Model
Best practices recommend creating FULL backups of the Model Database, and doing so only when necessary for your business needs. It is a small database that rarely sees changes; however, it is important to make sure it is backed up especially immediately after customizing its database options.
MSDB
Microsoft recommends to perform backups on the MSDB database whenever it is updated.
TempDB
What about TempDB? Isn’t it a System Database as well? Despite the importance of backups and recovery, the only database that cannot be backed up or restored is TempDB! Why can it not be backed up or restored? TempDB is recreated each time the server is restarted, so any temporary objects like tables, indexes, etc, are cleared automatically. As seen here, backup nor recovery are even an option!
“But, backups take so long to run!”
Feeling like this might be too much trouble? As with any other backup, these too can be automated by using a SQL Agent job! There is absolutely no reason NOT to back up your system databases. If you feel otherwise, might I suggest you keep an updated resume close at hand.
Leave a Reply