Database Backup Strategies Every Developer Should Know (Before Disaster Strikes)

You have never needed a backup until you need one desperately. Then: the database is corrupt, the accidental DELETE had no WHERE clause, the migration went wrong and there is no going back, or the server's disk died. In that moment: your backup strategy is the difference between "30 minutes of data loss" and "catastrophic business failure."

Here is how to think about backups and what to actually implement.

The Backup Axiom

A backup you have not tested restoring is not a backup. It is a hope. Files sitting in S3 that you have never verified: you do not know if they contain valid data. You do not know if the restore process works. You will discover this during the worst possible moment.

Test your restore process regularly. Monthly: download a backup, restore it to a test environment, and verify the data is complete and correct. This is non-negotiable.

Backup Types

Full backup: a complete copy of the entire database at a point in time. Large (contains everything), slow to create, but simple to restore.

Incremental backup: only the changes since the last backup. Small, fast to create, but requires the previous full backup plus all incrementals to restore. More complex restoration.

Continuous archiving (WAL/Binlog): database writes are streamed continuously to storage. Allows point-in-time recovery (restore to any second, not just when backups were taken). The gold standard for production databases.

What to Back Up

The database itself: obviously. But also: migration state (which migrations have been applied — needed for restore consistency), configuration (database settings, connection parameters), and schema documentation (helps verify restore correctness).

What NOT to back up: cached data (can be regenerated), session data (ephemeral by nature), and temporary tables (rebuilding is cheaper than backing up).

Backup Schedule for Small Teams

Minimum viable backup strategy:

Daily automated full backup: scheduled at low-traffic time (3am). Uploaded to external storage (S3, GCS — NOT on the same server as the database).

Retain 7 daily backups: one week of daily recovery points. If you discover corruption on Thursday: you can restore to any day in the past week.

Retain 4 weekly backups: one month of weekly recovery points. For problems that go unnoticed for days.

Retain 3 monthly backups: three months of monthly recovery points. For "we need data from two months ago" situations.

This is not enterprise-grade. But it covers 95% of disaster scenarios for small applications at minimal cost.

Implementation: Managed Databases (The Easy Path)

If you use managed databases (Planetscale, Supabase, AWS RDS, Neon): backups are included. Automatic daily backups, point-in-time recovery, and managed retention.

Verify: what is the backup frequency? (Daily? Continuous?) How far back can you restore? (7 days? 30 days?) Is point-in-time recovery available? (Can you restore to 2:37pm, not just "yesterday's backup"?) What is the restore process? (Self-service? Support ticket? How long does it take?)

Our setup (Planetscale): automatic daily backups, 7-day retention, point-in-time recovery available. We have tested restore: create a branch from backup, verify data, promote to production if needed.

Implementation: Self-Hosted Databases

If you self-host: backups are YOUR responsibility. Nobody else is doing them.

PostgreSQL: use pg_dump for logical backups (SQL-readable, portable) or pg_basebackup for physical backups (faster restore, larger files). Schedule with cron. Upload to S3.

MySQL: use mysqldump for logical backups or Percona XtraBackup for physical backups (online, non-locking). Same scheduling approach.

SQLite: copy the file. Literally: cp database.sqlite backup-YYYY-MM-DD.sqlite. SQLite backups are trivially simple (it is one file). Use sqlite3 .backup for a consistent copy during active writes.

Critical: do NOT store backups on the same disk as the database. If the disk fails: you lose both production data AND backups. Upload to external storage (S3, separate server, different cloud region).

Disaster Recovery Scenarios

Scenario 1 — Accidental data deletion: developer runs DELETE without WHERE clause. Data is gone from production. Recovery: restore the most recent backup. Data loss = time since last backup. With daily backups: maximum 24 hours of data loss. With continuous archiving: seconds of data loss.

Scenario 2 — Database corruption: disk failure, crash during write, or software bug corrupts the database file. Recovery: restore from the last known-good backup. Verify integrity before promoting.

Scenario 3 — Failed migration: schema migration applied to production that drops a column or table. Data is permanently lost from the current state. Recovery: restore pre-migration backup. Re-apply the migration correctly.

Scenario 4 — Ransomware/attack: entire server compromised, database encrypted or destroyed. Recovery: restore from offsite backups (stored in a separate account/location that the attacker cannot access).

Recovery Time and Recovery Point

RTO (Recovery Time Objective): how long can you be down? If your application is down for 4 hours: is that acceptable? RTO determines: how fast your restore process needs to be.

RPO (Recovery Point Objective): how much data can you lose? If you lose the last 24 hours of user signups: is that acceptable? RPO determines: how frequently you need to backup.

For our application: RTO = 2 hours (we can be down for 2 hours without catastrophic business impact), RPO = 1 hour (losing more than 1 hour of data is unacceptable). This means: backups at least every hour, and restore process takes under 2 hours.

The Backup Verification Process

Monthly: download the latest backup, restore to a test database instance, run basic queries (user count, recent records, specific known data), compare with production (are the numbers reasonable?), and document the result (backup verified on [date], restore took [time], data verified correct).

If restore fails: fix it NOW. Not later. A broken backup discovered during a disaster is the worst possible timing.

Cost of Backups

S3 storage: approximately $0.023/GB/month. A 10GB database backed up daily for 30 days: 300GB storage = $6.90/month. With weekly and monthly retention: maybe $10-15/month total.

Compute for backup creation: minimal. pg_dump takes minutes for databases under 50GB.

Cost of NOT having backups: catastrophic. Data loss can destroy a business. $10/month for backup storage is the cheapest insurance you will ever buy.

The Common Mistakes

Mistake 1: backups on the same server. Disk dies → lose data AND backups simultaneously.

Mistake 2: never testing restore. Backup files exist but the restore process is untested and might fail.

Mistake 3: no retention policy. Storing only the most recent backup means: if corruption goes unnoticed for 2 days, your only backup is also corrupt.

Mistake 4: unencrypted backups. Backups contain all your data. If stored unencrypted in S3 with misconfigured permissions: it is a data breach waiting to happen. Encrypt backups at rest.

Mistake 5: no monitoring. Backup cron job fails silently for 3 weeks. When you need to restore: the most recent backup is 3 weeks old. Monitor backup jobs. Alert on failure.

The One Command You Should Know

Practice this before you need it: the complete restore process for your specific database. Write it down step by step. Store it where you can find it at 2am under pressure. When disaster strikes: you follow the documented procedure, not debug the restore process in a panic.