Home / Databases

DatabasesHow-toIntermediate

Database backup strategy foundation: an RPO/RTO matrix, schedules, retention and restore tests

Turn RPO and RTO targets into backup schedules for each engine. Covers retention rules, the 3-2-1-1-0 approach with immutable copies, and a restore testing routine that proves the backups work.

Deepak Bhardwaj3 min read

Applies toSQL Server, PostgreSQL, Oracle, MySQL; managed database services; backup tools such as Veeam, Commvault and Rubrik

1. Start from RPO and RTO

  • RPO (recovery point objective): how much data you can lose. This sets log backup frequency.
  • RTO (recovery time objective): how long recovery can take. This sets the restore method (restore from backup, a standby, or snapshots).
RPO target Backup approach
24 h Daily full
1 h Daily or weekly full + hourly log/WAL backups
5–15 min Log/WAL backups every 5–15 min, or continuous WAL archiving
~0 Synchronous replica (HA/DR), with backups as well, not instead
RTO target What makes it achievable
Days Restore from offsite or tape
Hours Restore from disk; database size ÷ measured restore throughput must fit
Minutes Standby replica, or storage snapshots

2. Per-engine schedules (Tier 1 example)

Engine Full Differential / incremental Log
SQL Server Weekly Daily differential Every 15 min (FULL recovery model)
PostgreSQL Weekly (pgBackRest full) Daily (diff or incr) Continuous WAL archive
Oracle Weekly level 0 (RMAN) Daily level 1 Archivelog every 15–30 min, with block change tracking
MySQL Weekly full (XtraBackup / MySQL Enterprise Backup) Daily incremental Binlogs copied every 5–15 min

3. Retention

  • Operational restores: 14–35 days on fast storage.
  • Compliance: monthly or yearly copies for as long as policy or regulation requires, on cheaper storage.
  • Log and WAL backups must cover the whole period back to the oldest full you keep.

4. 3-2-1-1-0

  • 3 copies, on 2 media types, 1 offsite.
  • 1 immutable or air-gapped copy (object lock, a hardened repository, vault lock) against ransomware.
  • 0 errors: restores verified.

Backup credentials must not be domain admin, and backup storage must not be deletable by DBAs' everyday accounts.

5. Restore testing

Frequency Test
Daily (automated) Restore the latest backup of a sample database to a test server, then run an integrity check (DBCC CHECKDB, pg_amcheck, RMAN VALIDATE)
Quarterly Point-in-time restore of every Tier 1 database; measure the time against the RTO
Yearly Full site recovery from offsite or immutable copies 
# SQL Server automated restore test with dbatools
Test-DbaLastBackup -SqlInstance sql-prd-01 -Destination sql-test-01 -Database Sales

6. Monitoring

  • Alert on a failed backup, and on a missed backup (no successful log backup in 2× the interval).
  • Track the restore throughput (GB/hour) so RTO numbers are based on evidence.

Warning

Replicas are not backups. A DROP TABLE or ransomware encryption replicates to the standby within seconds. Keep point-in-time backups even when you have HA and DR.

Related fixes