What should my backup strategies be for system databases?

If you're a DBA, you'll say, it's simple; in the maintenance plan, just select 'all databases' or if you rely on tape backups (file level), then there's no question.

 

Why would I write a blog about it? Everything goes fine until we have the system databases working, the moment we don't have backups for system databases, we lose logins, metadata information, information about replication in case you lose distribution database, and most importantly, when your team relies on you to perform any RCA!!! (Now, I'm sure you'll say, YES)

 

Say you have around 800 instances, pull out a report on how you have configured the backups for system databases (some of your instances would be installed in C drive and the system databases go inside C drive but you'll not have backup strategies for C drive, you'd have it only for data & log drive.)

Master database

Do I need to take the backup? Big yes. 

Default recovery model is simple 

Note: If you perform any change ("Change management"), you perform the backup/restore for your user database but you tend to forget taking a full backup of master database. Don't do that! Backup your master database also, after-all it's master ;-)

Model database

Even though it's just a template, if you made any standard changes, you'll need a full backup of model database to retain that change in case of disaster.

The recovery model is user configurable. However I'd keep it simple as we hardly make changes to model.

Msdb database

Yes, msdb backups are required. I have restored msdb databases 100's of times as a user database to get the metadata information while performing the RCA (say a database was dropped due to corruption and later restored from a good known backup but you'd want to know which pages were bad before it was dropped as a part of RCA.) You can go back and search for msdb..sys.suspect_pages to get that information.

Resource database

Big no! Simply because you can't back it up. It's a read-only database and yes, I'll not waste your time on this.

Tempdb database

You cannot back up the tempdb system database. You'll know what tempdb is for, "A workspace for holding temporary or intermediate result sets. This database is re-created every time an instance of SQL Server is started. When the server instance is shut down, any data in tempdb is deleted permanently". So that explains it all.

Distribution Database

It only exists if you're using replication which (unfortunately) is true for most of the enterprise companies! Just kidding! It has several features which cannot be replaced by any of the other DR/HA solutions, we'll discuss about that some other day. The default recovery model for distribution is simple and that's enough.

 

OK, I accept, it's a simple blog (Picks from Technet article). My request to whoever reads this blog is to add their experience in the comments section which you think goes with the subject and I'll add that information post validation.

-KKB

Comments

• Anonymous
  July 26, 2015
  Very useful!