Backup and Restore Runbook

Backup and Restore Runbook: Back Up Daily, Restore Monthly

Backups have an uncomfortable truth: a "completed" backup does not mean a "usable" backup. Many sites run backup scripts daily and the logs look healthy — until the day they actually need to restore, and discover the backup file is corrupt, incomplete, or stored only on the same machine as the data it was protecting. So this runbook revolves around one idea: backup frequency is set by how much loss your business can tolerate, while restore-drill frequency determines how trustworthy your backups really are.

Backup Strategy Overview

Incremental restores depend on the full-plus-incremental chain being intact, so beyond the daily full backup, the monthly drill should verify that "latest full backup + all incrementals" can actually reproduce the newest data. Longer retention is not always better: 30 days covers accidents and data loss, while older data has little value and keeps consuming storage and drill effort.

How to Back Up a Database

For MySQL, a full dump plus binlog incrementals is a common combination:

# Daily full backup at 03:00
mysqldump --single-transaction --routines --triggers \
  -u backup -p$DB_PASSWORD yourdb \
  | gzip > /backup/mysql/db_$(date +%F).sql.gz

# Enable binlog as the incremental layer
mysql -e "SET GLOBAL log_bin=ON;"

# Keep 30 days, clean up older files
find /backup/mysql -name "*.sql.gz" -mtime +30 -delete

For PostgreSQL, use pg_dump -Fc (custom format) with WAL archiving — same idea.

Offsite Copies and the 3-2-1 Rule

Repeat the 3-2-1 rule: 3 copies of the data, on 2 different media, with 1 copy stored offsite. A server is not a backup — disk failure, a datacenter outage, or ransomware can take down "the backups on the same machine" along with everything else. At least one copy should be synced to object storage or another machine:

# Sync backups to object storage with rclone
rclone copy /backup/mysql b2:bucket-16idc/mysql --transfers 8

Encrypt before syncing: object storage does not keep secrets for you — anyone with the access key can read the data. Encrypt locally with gpg --symmetric or age before upload and store the key separately. Make the restore drill cover the full "decrypt + restore" chain; verifying only that the file can be downloaded is not enough.

The Standard Monthly Restore Drill

1. Set up a clean environment (a fresh server or container) matching production
2. Restore from the most recent backup to a point in time
3. Verify data consistency: row counts, key-table checks, timestamp of the newest record
4. Record three metrics: restore duration, whether data is consistent, issues found
5. Turn any issues into improvement items and fix them before the next drill

Keep drill records in a fixed format, for example:

Choosing a Backup Scheme: Full, Incremental, or Differential

Start by answering one question: what recovery time (RTO) can the business tolerate? The RTO directly determines the scheme — if you need restoration within 30 minutes, "full + latest differential" beats "full + a chain of incrementals," because there are fewer files to replay.

For small and mid-sized sites, my advice is to start with "daily full + hourly incremental" and a monthly restore drill. Once the data grows, evaluate whether a differential or snapshot scheme is worth adding — do not adopt a complex backup architecture from day one.

Monitor the backups themselves: the backup script should also be monitored. Have it notify on both success and failure, retry once automatically on failure, and alert — so you never end up with a silently failing backup that nobody noticed.

Common Problems

• Backups run but will not restore: usually caused by missing --single-transaction (inconsistent data during the dump) or a corrupted archive. Restore drills exist precisely to catch this.
• The disk fills up: the backup script has no cleanup policy, so it fills the system disk and takes the service down with it. Always add -mtime cleanup and a disk alert.
• Lost encryption keys: a backup encrypted with a single key that is lost is as good as no backup. Store keys separately and manage them properly.
• Backing up the database but forgetting the files: user uploads and static assets often live on the same server; restoring only the database gives you half a site. Include "database + file assets + configuration" together in the backup strategy.

Reference: MySQL mysqldump docs https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html
Reference: PostgreSQL backup and restore https://www.postgresql.org/docs/current/backup.html
Reference: Veeam on the 3-2-1 backup rule https://www.veeam.com/blog/backup-rule-3-2-1.html