Nightly Backups Run. Are You Actually Covered?
Scenario
Every night, an automated job dumps the production database, and every morning the log says "backup completed successfully". It has said that for months without a single failure. The dump files land on the same server that runs the database. Nobody, at any point, has ever restored one of them.
The Quick Fix on the Table
Technically the proposal is to do nothing: a teammate argues the team is covered — the automation runs, the success messages are green, the files exist. Disaster recovery: done. Why spend time on something that already works?
The quick fix is on the table and the room is waiting for your call. Would you sign off on it? Take a position and justify it — out loud or on paper — before revealing the analysis.
Why the Quick Fix Fails
- A backup is only proven by a restore. "Completed successfully" means the job exited zero — not that the dump is complete, uncorrupted, importable, or usable by the current database version. Truncated dumps, skipped tables and encoding surprises all hide behind green logs.
- Same server means shared fate. The most common disasters — disk failure, filesystem corruption, an attacker with server access, an accidental instance deletion — destroy the database and its backups in the same event. That is not redundancy; it is one copy with extra steps.
- Nobody knows how long recovery takes. If a restore has never been run, the first rehearsal happens during a real outage: locating files, remembering flags, discovering the restore takes six hours — all while the business is down and asking for an ETA nobody can give.
- Nobody has agreed on what losing a day means. Nightly dumps imply up to 24 hours of lost transactions. Maybe that's fine — but that is a business decision, and right now it has been made silently by a cron schedule.
- Untested procedures rot. Schemas grow, credentials rotate, extensions get added, versions change. A restore path that worked in theory a year ago quietly stops working, and only a real test notices.
The interviewer nods: “Fine, the quick fix is off the table. So what exactly would you do — step by step?” Sketch your plan before revealing the approach.
The Right Approach
- Run one restore this week — as a test. Spin up a scratch instance and restore last night's dump into it (
pg_restore/mysql < dump.sql). This single exercise answers the biggest question — do the backups work at all — and measures how long recovery takes. - Define RPO and RTO with the business. Agree how much data loss is acceptable (RPO) and how long the service may be down (RTO). These two numbers decide whether nightly dumps suffice or you need WAL archiving / binlog shipping for point-in-time recovery.
- Get copies off the server. Ship every backup to independent storage — for example
aws s3 cpto a versioned bucket in another region, ideally with object lock so a compromised server cannot delete its own backups. Follow the 3-2-1 shape: three copies, two media, one off-site. - Monitor outcomes, not exit codes. Alert on missing or undersized backup files and on backup age, not just on job failure — a cron job that silently stopped running produces no error to alert on.
- Automate a periodic restore test. A scheduled job that restores the latest backup into a throwaway instance and runs sanity queries (row counts, key tables present) turns "we believe" into "we verify" — continuously.
- Write the runbook while calm. Document the exact restore commands, where credentials live, and who does what, so the real event is execution rather than improvisation. Re-run the drill after major schema or version changes.
Final pushback: “Your plan costs more time and money than the quick fix. Convince me.” How do you defend your position under pressure?
How to Defend It
- "The log proves the job ran, not that the data comes back. The only proof of a backup is a restore, and we have never done one."
- "Our backups live on the machine they protect. The disk failure that kills the database kills the backups in the same second — that's one copy, not a recovery plan."
- "If the CEO asks 'how long until we're back and how much did we lose?', today our honest answer is 'unknown and up to a day'. RTO and RPO should be decisions, not surprises."
- "One afternoon restoring into a scratch instance either confirms we're covered or finds the problem while it's free to find. Every alternative discovery moment is a disaster."
- "I'm not asking to rebuild anything — the nightly job stays. I'm asking to add an off-site copy and a restore test, which is the difference between having files and having recovery."