Almost everyone has backups; far fewer people are sure they can restore from them. A backup that has never been tested is, at best, a hope. At worst it's a file that looks big enough and gets created every night, but is truncated halfway through, and you only find out on the day you need it.

This article builds a simple but reliable backup setup for an ordinary server: what to back up, how to take a consistent dump, how to verify it, where to keep it and how to rehearse a restore.

Short answer: The 3-2-1 rule means three copies of your data (the original plus two backups), on two different types of media or systems, with at least one kept off-site. A backup is only trustworthy if it is taken consistently, verified before it is accepted, and restored regularly on a separate machine as a test.

The 3-2-1 rule

The oldest and still most useful rule in this area:

  • 3 copies of the data: the original and at least two backups.
  • On 2 different media or systems: for example, the server's own disk and separate storage.
  • 1 copy off-site: on another server, in another data center, or even with another provider.

The logic is simple: each copy protects against a different kind of disaster. A copy on the same server saves you from an accidental DROP TABLE, but if the disk fails or the provider closes your account, it disappears with the server. The off-site copy is for that day.

These days one more condition is usually added: at least one copy must be impossible to delete from the main server. If someone breaks into the server and the same SSH key used to send backups can also delete the off-site copies, you've followed 3-2-1 on paper but not in practice.

What to back up

Databases: dump, don't copy files

Copying /var/lib/mysql or /var/lib/postgresql while the service is running usually produces an inconsistent copy: some files were copied before a transaction and some after. The right way is a logical dump with the database's own tools.

For MySQL and MariaDB with InnoDB tables:

bash
mysqldump --single-transaction --quick --routines --triggers --events app | gzip > app.sql.gz

--single-transaction takes the whole dump inside one transaction with a consistent snapshot, without locking tables, so the site keeps working during the backup. This only holds for InnoDB; MyISAM tables are not kept consistent by this option. --quick reads rows one at a time instead of loading large tables into memory in one go.

For PostgreSQL, pg_dump takes a consistent snapshot by design. The custom format (-Fc) is compressed and allows selective restores later with pg_restore:

bash
sudo -u postgres pg_dump --format=custom app > app.dump

Never put the database password on the command line; it is visible to every user in the process list. For MySQL, create an options file with 600 permissions; for PostgreSQL, use the postgres system user or a ~/.pgpass file:

/root/.backup.my.cnf
[client]
user=backup
password=CHANGE_ME

The backup user should have read-only privileges:

sql
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'CHANGE_ME';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS ON *.* TO 'backup'@'localhost';

Files

Alongside the database you usually need:

  • Files uploaded by users (for example storage/app in Laravel).
  • Server configuration: /etc/nginx, /etc/caddy, crontabs, systemd units.
  • The .env file and the application's encryption keys. Without APP_KEY, some encrypted data in the database can no longer be read. These files contain secrets; keep their backups encrypted or somewhere with restricted access.

Application code normally lives in Git and doesn't need a separate backup, provided you're sure what's on the server actually matches the repository.

Backup types at a glance

Type What is stored Restore Space needed Note
Full All data on every run Simplest: one file High The best starting point for small and medium databases
Incremental Only changes since the last backup Slower: last full backup plus every incremental, in order Low If one link in the chain is broken, every later backup is useless
Snapshot Image of a disk or volume at one moment Fast, but usually restores the whole disk Depends on provider Only crash-consistent for a running database, and usually stays with the same provider

For most small projects, a daily full dump plus a weekly provider snapshot is a sensible combination. Don't let the snapshot replace the dump: it doesn't count as off-site and doesn't guarantee database consistency.

A small, reliable backup script

The script below creates a timestamped dump on each run, verifies it before accepting it, deletes old copies and sends everything to another server:

/usr/local/bin/backup-db.sh
#!/usr/bin/env bash
# Daily database backup: dump, verify, rotate, copy off-site.
set -euo pipefail
umask 077

DB_NAME="app"
BACKUP_DIR="/var/backups/db"
KEEP_DAYS=14
MIN_BYTES=10240
REMOTE="backup@backup.example.com:/srv/backups/app/"
SSH_KEY="/root/.ssh/backup_ed25519"

STAMP="$(date +%F_%H%M)"
OUT="$BACKUP_DIR/${DB_NAME}_${STAMP}.sql.gz"

mkdir -p "$BACKUP_DIR"
# A half-written file must never look like a finished backup.
trap 'rm -f "$OUT.part"' EXIT

# 1. Dump into a temporary name
mysqldump --defaults-extra-file=/root/.backup.my.cnf \
  --single-transaction --quick --routines --triggers --events \
  "$DB_NAME" | gzip -6 > "$OUT.part"

# 2. Verify before accepting it
gzip -t "$OUT.part"

size="$(stat -c %s "$OUT.part")"
if (( size < MIN_BYTES )); then
  echo "ERROR: dump is only $size bytes" >&2
  exit 1
fi

last_line="$(zcat "$OUT.part" | tail -n 1)"
if [[ "$last_line" != "-- Dump completed"* ]]; then
  echo "ERROR: dump does not end cleanly: $last_line" >&2
  exit 1
fi

mv "$OUT.part" "$OUT"

# 3. Rotate local copies
find "$BACKUP_DIR" -maxdepth 1 -type f -name "${DB_NAME}_*.sql.gz" \
  -mtime +"$KEEP_DAYS" -delete

# 4. Copy off-site
rsync -a -e "ssh -i $SSH_KEY -o BatchMode=yes" "$BACKUP_DIR/" "$REMOTE"

echo "$(date -Is) OK $OUT ($size bytes)"
bash
sudo install -m 700 backup-db.sh /usr/local/bin/backup-db.sh
sudo /usr/local/bin/backup-db.sh

A few parts of this script look minor, but they are where all its value lies.

Why pipefail is essential

In mysqldump ... | gzip > file, the exit status of the whole line is by default the status of the last command, gzip. If mysqldump fails halfway, gzip happily compresses the half it received and the script finishes "successfully." set -o pipefail makes the failure of any part of the pipeline fail the whole line.

Three checks that the dump isn't truncated

  • gzip -t checks the integrity of the compressed file. A file whose writing was cut short usually fails here.
  • Size: if the dump is suddenly only a few kilobytes, something went wrong, such as a failed database connection or the wrong (empty) database. Set MIN_BYTES to suit your own data.
  • Last line: when mysqldump finishes completely, it writes a line starting with -- Dump completed on at the end of the file. If that line is missing, the dump stopped before the end. (The line only appears if you haven't used --skip-comments.)

Writing to .part and renaming with mv at the end guarantees that a file with the final name has always passed all three checks.

The PostgreSQL version

For PostgreSQL only the dump and check steps change. The custom format is already compressed, so gzip -t and the last-line check don't apply; instead, read the whole file with pg_restore and discard the output. If the file is truncated or corrupt, pg_restore exits with an error:

bash
OUT="$BACKUP_DIR/${DB_NAME}_${STAMP}.dump"
runuser -u postgres -- pg_dump --format=custom "$DB_NAME" > "$OUT.part"
pg_restore --file=/dev/null "$OUT.part"

pg_restore --list also exists, but it only reads the table of contents from the start of the file and isn't enough to detect truncation.

Rotation with find -mtime

-mtime +14 matches files last modified more than 14 full days ago. Two precautions: -maxdepth 1 and an exact -name pattern stop anything unintended from being deleted, and before adding -delete, run the same command once without it to see the list of files.

The off-site copy with rsync

The rsync command deliberately has no --delete. If the local folder ever ends up empty or corrupted, --delete would faithfully replicate that damage to the off-site copy. Rotate the off-site copies with a separate find on the backup server itself, and keep a longer retention period there.

Better still is pulling instead of pushing: the backup server reads the files from the main server with rsync. That way the main server holds no key that can write to or delete from the backup server, and a breach of the main server doesn't threaten the off-site copies.

Scheduling with cron

Create a file in /etc/cron.d. Unlike a user crontab, files in this directory have a sixth column for the user to run as:

/etc/cron.d/backup-db
SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
30 3 * * * root /usr/local/bin/backup-db.sh >> /var/log/backup-db.log 2>&1

Two details that silently break cron: file names in /etc/cron.d must not contain a dot, and the file must end with a newline. Schedule the run for when server load is low.

A script that fails without anyone noticing is no better than no script. Check tail /var/log/backup-db.log at least once a week, or if mail is configured on the server, add MAILTO to the same file to have errors sent to you.

The most important part: testing the restore

Everything so far only makes a healthy backup more likely. The only way to be sure is an actual restore. The best place for it is a separate server or virtual machine, and the best file is the off-site copy, because then you've tested the rsync path too.

For MySQL, on the test machine:

bash
sudo mysql -e 'CREATE DATABASE restore_test'
zcat app_2026-10-06_0330.sql.gz | sudo mysql restore_test
sudo mysql -e 'SELECT COUNT(*) FROM restore_test.users'
sudo mysql -e 'DROP DATABASE restore_test'

A warning: if the dump was taken with --databases or --all-databases, the file contains CREATE DATABASE and USE statements with the original name, and the restore goes into the original database, not restore_test. That's why the script above passes the database name without --databases. Never run a restore test on a production server.

For PostgreSQL:

bash
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore --no-owner --dbname=restore_test app_2026-10-06_0330.dump
sudo -u postgres psql -d restore_test -c 'SELECT COUNT(*) FROM users'
sudo -u postgres dropdb restore_test

On every test, answer these questions and write the answers down:

  • Did the restore finish without errors?
  • Do the row counts of a few key tables match what you expect? How recent is the newest record?
  • How long did the whole thing take? That number is how long the site will be down on the day of an incident.
  • Does the application start against the restored database? Are the uploaded files and .env in place too?

Put this test in your calendar once a month, and repeat it after any significant change to the database schema or the backup script.

Frequently asked questions

How often should I back up a website?

For most small projects, a daily full database dump plus a weekly provider snapshot is a sensible combination. In principle, the interval between backups should match how much data you can afford to lose.

Is a server snapshot a substitute for a database backup?

No. A snapshot of a running database is only crash-consistent, and it usually stays with the same provider, so it doesn't count as an off-site copy either. Treat it as a complement to a logical dump, not a replacement.

Why shouldn't I copy the MySQL data directory directly?

Copying database files while the service is running usually yields an inconsistent copy, because some files are copied before a transaction and some after. The correct approach is a logical dump with mysqldump using single-transaction for InnoDB tables, or pg_dump for PostgreSQL.

How do I know a backup file is valid?

Three quick checks: verify the compressed file with gzip -t, confirm the size isn't suspiciously small, and look for the Dump completed line at the end of the mysqldump output. The only certain proof, though, is an actual restore on a separate machine.

How often should I test restoring a backup?

At least once a month, and after any significant change to the database schema or the backup script. Restore from the off-site copy so the transfer path is tested too, and record how long the whole process takes.

Wrap-up

A good backup has three properties: it is taken consistently (--single-transaction and pg_dump), it is verified before being accepted (pipefail, gzip -t, size and last line), and at least one copy sits where neither a server failure nor an intruder can reach it. None of that replaces a restore test. Until you've brought a backup up on another machine at least once, you're only assuming you have one.