Today’s post is just a bunch of questions, but they are very important questions, all around the main topic of a Backup Retention Policy.
- How long do you keep backups for every database in your environment (including development, testing, quality assurance and production)?
- Do you keep backups of system databases?
- Do you keep backups of system databases in development, testing, quality assurance and production?
- Out of those database backups you keep, how many of them are just-in-case full backups, or even just copy-only backups (out-of-band)?
- Of the important ones with transaction log backups, do you also keep differential backups?
- How often are these differential backups taken?
- Do you check to see that the differential backup file sizes approach or exceed the size of a full backup?
- Of the transaction log backups, how often are these backups taken? Every fifteen minutes?
- How long do you keep transaction log backup files?
- How soon will it be until you run out of space for backups?
- What is your contingency plan for when you do run out of space?
- How do you test your backups?
- How frequently do you test your backups?
- Do you have an offsite backup solution?
Since each environment is unique, there is no perfect answer for these questions, but I urge you to consider them, especially in the context of the recovery time objective (RTO) and recovery point objective (RPO).
We may not want to retain three months of transaction log backups for a test environment that is purged every week, but we may want to ensure that the model or msdb databases are regularly backed up.
Want to talk about this with your employer or client? Check out Brent Ozar’s updated High Availability and Disaster Recovery Planning Worksheet to see how you fare in the case of a disaster.
Let me know in the comments, or on Twitter at @bornsql.