Do I need to backup system databases?

Great question!

Before I answer this right off, ask yourself these questions:

  1. “Do I want to lose access to all other databases on the instance?
  2. What would happen if I lose the model my company demands for the specific way every new database must be created?
  3. 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!

No-Backup
It is not possible to backup or restore TempDB

“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.

 

*Originally posted at Procure SQL:
https://www.procuresql.com/blog/2018/09/13/backup-system-databases/

 

Published by

Angela Tidwell

I am a constant student, a proud wife and mom, and I love to tell a good story. I also love hockey, car dancing, and singing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.