Backup and Restore Strategy: From RPO/RTO to Tested Recovery

A backup is only as good as its last successful restore—yet many organizations discover this truth only after a disaster reveals untested tapes, corrupted files, or missing transaction logs. A sound backup and restore strategy begins not with technology choices but with clear business requirements: how much data can you afford to lose, and how quickly must you be back online? These two numbers—Recovery Point Objective (RPO) and Recovery Time Objective (RTO)—drive every subsequent decision about backup types, scheduling, storage, and testing. This article walks through the complete lifecycle, from defining RPO/RTO to running recovery drills that prove your strategy works under pressure.

Defining RPO and RTO: Business Requirements First

RPO answers "how much data loss is acceptable?" measured in time (e.g., 15 minutes, 1 hour). RTO answers "how long until the system is usable?" measured in clock time (e.g., 2 hours, 24 hours). These are non-negotiable business metrics, not technical targets. A financial trading system might demand RPO of zero (no data loss) and RTO under 60 seconds, while a reporting warehouse may tolerate RPO of 24 hours and RTO of 8 hours.

Translate RPO into backup frequency: an RPO of 1 hour requires at least hourly backups. RTO dictates recovery infrastructure: faster RTOs demand pre-staged standby servers, automated failover scripts, or replica databases. Document these numbers for every critical database, and get sign-off from business stakeholders before designing the technical solution.

Rule of thumb: RPO divided by 2 gives your minimum backup frequency. If RPO is 1 hour, back up at least every 30 minutes. This buffer accounts for backup failures, retries, and propagation delays.

Backup Types and Scheduling Across Engines

All major engines support three fundamental backup types: full, differential, and incremental (transaction log). Full backups capture the entire database; differentials capture all changes since the last full; incrementals capture changes since the last backup of any type. Transaction log backups (SQL Server, PostgreSQL with WAL archiving, Oracle archived redo logs) enable point-in-time recovery.

Schedule full backups at the lowest frequency your RPO allows—typically daily for most systems. Interleave differentials every few hours to reduce recovery time (restore full + latest differential, then apply logs). For log backups, match frequency to RPO: every 5–15 minutes for tight RPOs, hourly for looser ones. Example scheduling for a PostgreSQL instance with 15-minute RPO:

# pg_dump full backup every Sunday at 0100
0 1 * * 0 pg_dump -Fc -f /backups/full_$(date +\%Y\%m\%d).dump mydb

# WAL archiving every 5 minutes (configured in postgresql.conf)
archive_mode = on
archive_command = 'cp %p /backups/wal/%f'
archive_timeout = 300

For MySQL/MariaDB with binary logs, use mysqldump for fulls and mysqlbinlog for incremental log application. SQL Server uses BACKUP DATABASE and BACKUP LOG. Oracle leverages RMAN with incremental level 0/1 backups and archived redo logs.

Storage, Retention, and Offsite Considerations

Store backups on separate storage from the production database—preferably on a different server, SAN volume, or cloud bucket. Use local fast storage for recent backups (to speed restores) and cheaper object storage (S3, Azure Blob, or on-premises tape) for older retention. Implement the 3-2-1 rule: three copies of your data, on two different media types, with one copy offsite.

Retention policies must balance compliance requirements (e.g., 7 years for financial data) against storage costs. Establish tiers: daily backups kept 30 days, weekly backups kept 12 months, monthly backups kept 7 years. Automate cleanup with retention scripts or backup software policies. Example retention logic for a directory of compressed backups:

# Keep last 7 daily, last 4 weekly, last 12 monthly
find /backups -name "*.dump" -mtime +7 -delete
# Weekly: keep files with "weekly" in name older than 28 days
find /backups -name "*weekly*" -mtime +28 -delete
# Monthly: keep files older than 365 days
find /backups -name "*monthly*" -mtime +365 -delete

Automating Backup Verification and Integrity Checks

A backup that cannot be restored is worthless. Automate verification immediately after each backup completes. For file-based backups (pg_dump, mysqldump), run pg_restore --list or mysql --one-database to check archive integrity. For native backups (SQL Server RESTORE VERIFYONLY, Oracle RESTORE VALIDATE), run the verification command in the same pipeline.

Go further: restore the backup to a non-production environment and run a checksum comparison against production data. Script this as a daily cron job that alerts on failure. Example for PostgreSQL:

#!/bin/bash
# verify_backup.sh
BACKUP_FILE="/backups/full_$(date +\%Y\%m\%d).dump"
pg_restore --list "$BACKUP_FILE" > /dev/null 2>&1
if [ $? -ne 0 ]; then
    echo "Backup $BACKUP_FILE is corrupt" | mail -s "BACKUP FAILURE" dba-team@company.com
    exit 1
fi
echo "Backup $BACKUP_FILE verified OK"

Designing a Recovery Workflow for Different Scenarios

Document step-by-step recovery procedures for at least three scenarios: single-table restore, full database restore to a point-in-time, and disaster recovery to a different region/data center. Each workflow must include the exact commands, expected output, and rollback steps if the restore fails.

For a point-in-time recovery (PITR) in PostgreSQL, the workflow is: stop the database, restore the last full backup, apply WAL archives up to the target time, and start the database in recovery mode. For SQL Server, use RESTORE DATABASE ... WITH NORECOVERY followed by RESTORE LOG ... WITH STOPAT. Store these workflows in a wiki or runbook accessible offline (e.g., printed copy in the data center).

Testing Your Restore: Drills, Metrics, and Iteration

Schedule quarterly recovery drills that simulate a full outage. Restore the most critical database to a separate environment, time the process, and compare against RTO. Measure two key metrics: backup integrity pass rate and actual restore time. If restore time exceeds RTO, identify bottlenecks—slow network, missing WAL segments, underpowered restore server—and fix them.

After each drill, hold a 15-minute retrospective: what worked, what broke, what documentation was missing. Update runbooks and automation accordingly. Track drill results over time; a trend of improving restore times validates your strategy, while regressions signal drift.

Common Pitfalls and How to Avoid Them

Pitfall 1: Untested restoration of encrypted backups. Always test decryption during drills—keys expire, certificates rotate. Store decryption keys in a secure vault with documented recovery procedures.

Pitfall 2: Ignoring backup of system databases. SQL Server's master and msdb, PostgreSQL's pg_global tables, and MySQL's mysql schema contain logins, jobs, and configuration. Back them up alongside user databases.

Pitfall 3: Over-reliance on a single backup tool. Use at least two methods—e.g., native backups plus file-system snapshots. If one tool fails, the other provides a fallback.

Pitfall 4: No monitoring of backup success. Set up alerts for any backup job that fails, takes too long, or produces zero-byte files. A silent failure discovered weeks later is a disaster waiting to happen.

The short version

Define RPO and RTO with business stakeholders before choosing technology. Schedule backups at double the frequency of your RPO. Store three copies on two media types, one offsite. Automate integrity checks immediately after every backup. Document and test recovery workflows quarterly, measuring restore time against RTO. Avoid common pitfalls by encrypting safely, backing up system databases, diversifying tools, and monitoring every job. A strategy that is never tested is just a hope—make recovery drills as routine as backups themselves.