How do you set your database backup frequency and retention policy?

1 Backup frequency

The frequency of your database backups depends on how often your data changes, how critical your data is, and how much storage space and bandwidth you have. Generally, you want to backup your database as often as possible, but not so often that it affects your database performance or consumes too much resources. A common approach is to use a combination of full, differential, and transaction log backups, which capture the entire database, the changes since the last full backup, and the individual transactions since the last backup, respectively. For example, you could perform a full backup weekly, a differential backup daily, and a transaction log backup every hour.

2 Backup retention

The retention of your database backups depends on how long you need to keep your data for legal, regulatory, or business reasons, and how much storage space and cost you can afford. Generally, you want to keep your database backups as long as possible, but not so long that they become obsolete or redundant. A common approach is to use a retention policy that defines how long you keep each type of backup, based on the recovery point objective (RPO) and the recovery time objective (RTO) of your database. For example, you could keep your full backups for one year, your differential backups for one month, and your transaction log backups for one week.

3 Backup location

The location of your database backups affects how secure, accessible, and reliable they are. Generally, you want to store your database backups in a different physical location than your database server, to avoid losing both in case of a disaster. A common approach is to use a backup location that offers encryption, compression, and redundancy, such as a cloud storage service, a network-attached storage (NAS) device, or a tape drive. For example, you could store your full and differential backups in a cloud storage service, and your transaction log backups in a NAS device.

4 Backup verification

The verification of your database backups ensures that they are valid, consistent, and restorable. Generally, you want to verify your database backups as soon as possible, and regularly test them for recovery. A common approach is to use a verification tool or command that checks the integrity, checksum, and header of your backup files, such as the RESTORE VERIFYONLY command in SQL Server. For example, you could verify your full and differential backups after each backup operation, and your transaction log backups before each restore operation.

5 Backup automation

The automation of your database backups saves you time, effort, and errors. Generally, you want to automate your database backups as much as possible, but not so much that you lose control or visibility of your backup process. A common approach is to use a backup automation tool or script that schedules, executes, and monitors your backup jobs, such as the SQL Server Agent or the pg_dump utility. For example, you could automate your full backup to run every Sunday at midnight, your differential backup to run every day at midnight, and your transaction log backup to run every hour on the hour.

6 Backup documentation

The documentation of your database backups helps you track, manage, and audit your backup activities. Generally, you want to document your database backups as detailed as possible, but not so detailed that it becomes overwhelming or outdated. A common approach is to use a backup documentation template or system that records the backup type, date, time, size, location, status, and notes, such as a spreadsheet or a database table. For example, you could document your full backup as:

Backup type: Full
Backup date: 2021-01-10
Backup time: 00:00:00
Backup size: 10 GB
Backup location: Cloud storage service
Backup status: Success
Backup notes: Weekly full backup        

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?