Database Backup and Recovery Strategy

Every database will eventually suffer something bad: a failed disk, a botched deployment, an accidental DELETE without a WHERE clause, or ransomware. Whether that becomes a minor inconvenience or a business crisis depends almost entirely on decisions made months earlier. A database backup strategy is not just "we take a nightly dump". It is a plan for how much data you can afford to lose, how fast you need to be back, and proof that the restore actually works.

Start with two numbers: RPO and RTO

Before choosing tools, agree on two targets with the business, not just the IT team.

• Recovery Point Objective (RPO): the maximum amount of data, measured in time, you can afford to lose. An RPO of 24 hours means losing a full day of orders would be tolerable. An RPO of five minutes means it would not.
• Recovery Time Objective (RTO): how long the system can be down while you restore. An internal reporting tool might accept a day; an online shop might accept an hour at most.

These numbers drive everything else. A nightly backup gives an RPO of up to 24 hours. Restoring a 500 GB dump from remote storage might take several hours, which sets a floor on your RTO. Tighter targets cost more, so set them per system rather than demanding the strictest everywhere.

Types of database backup

Logical backups

A logical backup exports data as SQL statements or a portable archive. It is easy to inspect, works across versions, and lets you restore a single table. The downside is speed: large databases take a long time to dump and even longer to restore, because every index must be rebuilt.

# MySQL: consistent dump of InnoDB tables without locking
mysqldump --single-transaction --routines --triggers \
  --databases shopdb | gzip > shopdb_2024-10-03.sql.gz

# PostgreSQL: compressed custom-format archive
pg_dump -Fc -d shopdb -f shopdb_2024-10-03.dump

Physical backups

A physical backup copies the database's data files directly. It is much faster to restore for large databases. Tools include Percona XtraBackup and MySQL Enterprise Backup for MySQL, and pg_basebackup or pgBackRest for PostgreSQL. Physical backups usually must be restored onto the same major version.

Full, incremental and differential

Point-in-time recovery

Nightly backups alone cannot recover the state at 3:47 p.m., just before someone dropped a table. Point-in-time recovery (PITR) combines a base backup with the database's continuous change log:

• In MySQL, this is the binary log (binlog). Restore the last full backup, then replay binlog events up to just before the mistake using mysqlbinlog --stop-datetime.
• In PostgreSQL, it is the write-ahead log (WAL). With WAL archiving enabled, you restore a base backup and set recovery_target_time to the moment you want.

PITR is how you get a low RPO without taking full backups every few minutes. Most managed cloud databases offer it as a setting; make sure it is switched on and that the retention window is long enough to notice mistakes before the logs expire.

Where to keep backups: the 3-2-1 rule

A widely used guideline is 3-2-1: keep three copies of your data, on two different types of storage, with one copy off-site. For databases, that might mean the live database, a backup on a separate server or volume, and an encrypted copy in cloud object storage in another region.

Two further protections have become important:

• Immutability. Ransomware often targets backups first. Object storage with versioning or write-once retention locks prevents backups from being deleted or overwritten, even by an attacker with credentials.
• Separate credentials. The account that writes backups should not be able to delete them, and production servers should not hold keys to the off-site copy's admin console.

Encrypt backups at rest and in transit. A backup contains everything an attacker would want, and is often protected less carefully than the live database.

Replication is not a backup

A replica copies every change from the primary database within seconds, including the accidental DROP TABLE. Replication protects you against hardware failure and helps availability, but it does nothing for human error or corruption. You need both. A delayed replica, deliberately kept an hour or so behind, can act as a quick safety net, but it does not replace real backups.

Test your restores

A backup you have never restored is a hope, not a plan. Common surprises discovered too late include dumps that silently failed weeks ago, missing stored procedures, encryption keys nobody can find, and restores taking far longer than the RTO allows. Build restore testing into routine operations:

1. Automate a regular test restore to a separate server, for example weekly.
2. Run sanity checks on the restored copy: row counts on key tables, the latest order date, and a few application smoke tests.
3. Time the restore and compare it with your RTO.
4. Practise PITR at least occasionally, since it is the procedure you will be doing under pressure.
5. Write a runbook: who does what, where credentials live, and the exact commands.

Monitoring the backups themselves

Alert when a backup job fails, when it runs much shorter or produces a much smaller file than usual, and when the newest backup is older than expected. Silent failure is the most common way backup strategies collapse. Our database management and server management services include backup design and restore testing, and our cloud solutions team can set up cross-region, immutable storage.

Key takeaways

• Define RPO and RTO first; they decide backup frequency, type and storage.
• Combine full backups with binlog or WAL archiving for point-in-time recovery.
• Follow 3-2-1, encrypt backups, and make at least one copy immutable.
• Replication is not a database backup, and an untested backup is not a backup either.