SQL Server Master Database Backup Frequency – How Often to Backup

backupsql serversql-server-2008-r2

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?

Best Answer

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