0

We have defined a data retention policy for our user databases, where our full backups are retained for 90 days, diffs are retained for 30 days, and logs are retained for 48 hours. This meets our business needs, and works well. We use Ola Hallengren's SQL Server Backup solution.

In addition, we take full backups of our system files (master, model, msdb, SSISDB). I recently noticed that these backups have no cleanup time defined, leading to indefinite retention. I've looked for what is the best practice concerning retention of system database backups, but cannot find any guidance. Just that it is best practice to take full backups of these databases.

I'd love to get these backups configured properly, and recover some of our drive space.

1 Answer 1

4

Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO) are the relevant terms around backup frequency and retention.

The Recovery Point Objective expresses the amount of data the business is comfortable losing. It might be expressed as "15 minutes" or "15 days" depending on the criticality of the data being backed up. An RPO of 15 Minutes means you should be taking backups every 15 minutes (probably log backups, for SQL Server databases in full recovery model).

The Recovery Time Objective defines how long your database system can be down while you are performing the restore process. Again, this is something the business should be defining.

Since you are asking about the system databases specifically, RPO and RTO for them should be centered around the contents of the databases and how critical they are for the business. The master database stores configuration information for the SQL Server instance, including logins, server-level permissions, etc. The msdb database contains all the SQL Server Agent jobs, schedules, etc.

The question you should ask the business is "how long are we comfortable being down?" If the answer is three days, and it takes you more than that to rebuild a server from the ground up, you should make certain that you can recover all the databases, including the system databases, in a shorter time than three days.

If you anticipate the need to restore system databases (so, logins, agent jobs, schedules, etc) from three years ago for regulatory purposes you should ensure you retain backups for at least that long.

In short, the only people who can tell you how frequently you should be taking backups, and how long you should be retaining them, is the business.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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