How to Rebuild a Broken Backup Schedule After a Failed Restore

A failed restore tells you your backup schedule was never really a backup schedule, it was a hope. The fix is to treat the failed restore as a test result: find out exactly which layer broke, repair that layer, then prove the repair with another restore before you trust the routine again. This guide walks through hosting backup schedule recovery from the mechanics up, so the next restore you attempt is boring.

Find out what actually failed before you change anything

Most failed restores are not storage failures. They are mismatches between what the backup job captured and what the restore job expected. The data was there; the shape of it was wrong. Before you touch cron or a scheduler, reproduce the failure and capture the exact error. Restore into a scratch directory or a throwaway instance, never over the live data, and keep the transcript:

$ tar -tzf /backups/site-2024-11-03.tar.gz | head -5
tar: This does not look like a tar archive
tar: Exiting with failure status due to previous errors

That output is a classic symptom of a truncated archive, which usually means the backup job was killed mid write, the disk filled up, or the dump was piped through a command that exited early. If the archive lists fine but the database restore fails, the problem is upstream: the dump was taken with the wrong flags, or it was taken while the application was writing and is internally inconsistent.

Check three things in order. First, does the archive open at all? Second, does it contain the files or tables you expect, at the paths you expect? Third, does the application start against the restored copy? Each failure points at a different part of the schedule, and fixing the wrong part wastes a cycle.

Audit the schedule itself, not just the last job

Look at what the scheduler actually runs, not what the documentation says it runs. On a typical Linux host that means the crontab for the backup user and any systemd timers:

$ crontab -l -u backup
15 2 * * * /usr/local/bin/backup.sh --full --keep 7 >> /var/log/backup.log 2>&1
$ systemctl list-timers --all | grep -i backup
backup-db.timer  backup-db.service  ...

Read the script that cron calls. The usual culprits are a relative path that only worked from someone's home directory, a `--keep` value that pruned the good copies before the bad ones, and a missing `set -e` so the script reported success after a failed step. A backup job that exits zero while doing nothing is worse than one that fails loudly, because your monitoring never fires.

Check the retention math against your restore needs. If the job keeps seven daily copies and the corruption happened days ago, the good data may already be pruned. Retention is part of the schedule, not a separate setting, and it needs to be long enough that a problem discovered on a Monday still has a clean copy from before it started.

Repair the backup job and its monitoring

Rewrite the job so that each stage fails independently and the whole thing exits non zero on any failure. A minimal shell job that verifies its own output looks like this:

#!/bin/sh
set -eu
DEST=/backups/site-$(date +%F).tar.gz
pg_dump -Fc appdb > "$DEST.dump"
tar -czf "$DEST" -C /var/www site
gzip -t "$DEST" && pg_restore -l "$DEST.dump" > /dev/null
echo "ok $(date -Is) $DEST"

The `gzip -t` and `pg_restore -l` lines are the important ones. They read the archive back and confirm it is structurally valid before the job claims success. A backup that has never been read is not a backup. Add the same check to your monitoring: alert on a missing "ok" line, not just on a non zero exit, because a job that never ran produces no error at all.

For database dumps, take them consistently. Use the native dump tool with a format that supports selective restore, and if the database is large, use a snapshot or a read replica rather than dumping a busy primary. Restoring a dump taken mid transaction is where "the backup was fine but the data was wrong" comes from.

Hosting backup schedule recovery: prove it with a restore drill

The only evidence that matters is a successful restore. Run one on a schedule, into an isolated environment, and record the result. A drill should restore the archive, start the application, and run a query that proves the data is present and recent:

$ pg_restore -d appdb_restore /backups/site-2024-11-03.tar.gz.dump
$ psql -d appdb_restore -c "select max(created_at) from orders;"
         max
---------------------
 2024-11-03 01:58:12

Compare that timestamp against the backup start time. A gap larger than your acceptable data loss window means the schedule is not meeting its target, even if every job exits zero. Automate the drill so it runs without a human remembering it, and treat a failed drill as a production incident with an owner and a follow up.

Finally, document the recovery path in the repository next to the backup script: where the archives live, how to decrypt them, which credentials the restore needs, and who is allowed to run it. The person who discovers the schedule is broken is rarely the person who wrote it, and a runbook that has been tested once is worth more than a dashboard that has never been read.

What to do next

Pick one service, run a restore into a scratch environment this week, and time it. Whatever breaks, fix that single layer, then repeat the drill until it passes twice in a row. Only after that should you widen the routine to the rest of your hosts and tighten retention, because a schedule you have proven beats a schedule you have merely configured.