Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The suggestion in BOL is fairly vague:

Back up master as often as necessary to protect the data sufficiently for your business needs. We recommend a regular backup schedule, which you can supplement with an additional backup after a substantial update.

If you venture further, you will find these details:

The types of operations that cause master to be updated, and that require a backup to take place, include the following:

  • Creating or deleting a user database.
  • If a user database grows automatically to accommodate new data,
    master is not affected.
  • Adding or removing files and filegroups.
  • Adding logins or other operations that are related to login security.
  • Database security operations, such as adding a user to a database, do not affect master.
  • Changing server-wide or database configuration options.
  • Creating or removing logical backup devices.
  • Configuring the server for distributed queries and remote procedure calls (RPCs), such as adding linked servers or remote logins.

So if all our logins are added through Windows groups, and we don't make any other changes to the database, does that mean that a one-time backup of the master is sufficient?

If not, what is the standard backup interval for the master database?

share|improve this question

3 Answers 3

up vote 11 down vote accepted

Your master DB is generally very small. Just back it up with the rest of your databases, at least daily. Does it matter?

Personally, when SHTF I'd like a master db backup that is a few hours old, even if I have 400 other identical ones going back 400 days. I don't want to think too much in the case I have to restore it...

share|improve this answer

All our production databases are backed up daily during a slow time. If operations were running 24/7 I would recommend once per 12 hours like the first answer.

Really the question is how much data and data structures can you afford to loose? If a meteorite destroyed your master database so there was only a smoking pit in the ground how fast could you bring up a backup to take it's place? You can laugh at the impossibility of an event that takes out your database but if business lines use it you will find there is very little tolerance for downtime.

If you backup daily how much new information could you loose in a day? How much time to replace/redo these changes? Can you redo the changes that were not backed up? How would you know....

Rather than focusing on someone else's policy you need to ask management what their tolerance for data loss and downtime is.

share|improve this answer
  1. Yes, if no changes to server (such as updates, adding logins, adding new databases etc) was made from last backup, you actually don't need to do the new backup. But I cannot understand what is the problem with this: backup of the master is fast opeartion, backup file is small.
  2. In my company master-backups are divided to 3 groups:
    • once per 12 hours (for servers where 10-50 of logins can be added during workday)
    • once per day (for most of the servers)
    • once per week (for little part of servers with infrequent changes)

It's not a standard, it's a company rule.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.