💾Backup database

Not one SQL keyword, an operational program

Backups combine base copies (full, differential, incremental) with continuous log chains (WAL, transaction log, binlog) so you can meet RPO/RTO. Managed clouds often hide raw BACKUP DATABASE behind APIs and automatic snapshots, application developers still need to understand what is restorable, how stale read replicas are, and where schema migrations live relative to data dumps.

⏱️RPO and RTO in one glance

How much data loss and downtime the business accepts drives architecture, not vice versa.

Term	Meaning
RPO	Recovery point objective: maximum acceptable data loss window (e.g. 5 minutes of writes).
RTO	Recovery time objective: maximum acceptable time to become read/write again after failure.

SQL

Logical dump (conceptual: usually shell, not psql)

-- Typical custom-format archive (parallel restore capable)
-- pg_dump -Fc -d biotech_app -f /backup/biotech_app_20260412.dump

✓Illustrative result set

note
Custom-format archive (-Fc) supports parallel restore

SQL

SQL Server native backup

BACKUP DATABASE biotech_app
TO DISK = 'S:\SQLBACKUP\biotech_app_20260412.bak'
WITH FORMAT, INIT, COMPRESSION, STATS = 10;

✓Illustrative result set

result
Backup complete

Logical vs physical

Logical dumps (pg_dump, mysqldump) are portable and selective; physical/base backups + log shipping replay faster for huge databases. Most production stacks use both: logical for partial restore, physical for DR time targets.

Restore drills

A backup that has never been restored is a hypothesis. Automate quarterly restores into an isolated account and run smoke queries plus migration replay from the same artifact pipeline you ship to prod.

⚡ Advanced / dialect note

RDS, Cloud SQL, Azure SQL, PlanetScale, etc. expose retention, cross-region copies, and PITR windows in the control plane, application code rarely runs BACKUP directly; instead configure policies and test failover runbooks.

Secrets and PII

Dumps often contain more than the database, verify encryption at rest, KMS keys, and that API keys in app configs are not accidentally bundled into the same tarball as the SQL dump.

TL;DR

Backups are copies of database state plus (for point-in-time recovery) continuous transaction logs. Logical dumps (e.g. pg_dump, mysqldump) are portable and good for subsets; physical/base backups replay faster at scale. A backup is not trusted until you have restored it on a schedule and run smoke queries, RPO/RTO are properties of your restore process, not the backup file alone.