DEV Community

Cover image for Your Database Has Backups. Have You Ever Tried Restoring One?

Your Database Has Backups. Have You Ever Tried Restoring One?

Robert Adamson on November 03, 2026

Your backup job ran successfully last night. This morning, a migration deleted the wrong data. You open the backup dashboard. Everything is green...
Collapse
 
kenwalger profile image
Ken W Alger •

One thing I'd add to the recovery drill is verifying that indexes and other database metadata are restored correctly. I've encountered several situations where the data came back successfully, but indexes were missing or outdated. The behavior can vary considerably depending on the database vendor, backup mechanism, and restore process.

A basic smoke test, such as checking whether record X exists, might pass without revealing the problem. Missing conventional indexes can cause serious performance degradation under realistic workloads, while missing or incorrectly restored vector search indexes can break retrieval in AI applications even when all the underlying documents and embeddings are present.

It's another reason to test representative application workloads after a restore, not just whether individual records survived. Successfully restoring the data and successfully recovering the system are two different things.

Collapse
 
amorizz profile image
Amorizz •

Section 3 hides a trap worth calling out. When a drill restore stops on role "app_readonly" does not exist, the quick way past it is pg_restore --no-owner --no-acl, and then the drill goes green while a real recovery would still be missing every grant. I'd keep a pg_dumpall --globals-only file next to each dump and restore it first, so the drill fails for the right reason. For step 5, I'd also give the restored app its own environment name in the error tracker, like restore-drill, so the errors it throws stay visible without paging whoever is on call.

Collapse
 
maame-codes profile image
Maame Afua A. P. Fordjour •

Love the idea of getting someone else to follow the steps too. If only the person who wrote it can follow it, that’s a problem

Collapse
 
arslan_ah profile image
Arslan Ahmad •

"Do you have a recovery process, or just backups?" is the question every team should ask before an incident forces it on them. A green backup dashboard tells you a job ran, not that you can get the app working again.

Starting from "define what you need to recover" is the right order. RPO and RTO are business decisions first; the tooling comes after. Restoring into an isolated environment is the only real test, because that's where you discover missing secrets, extensions, or a restore that takes 9 hours instead of 30 minutes.

Scheduled restore drills with the timing written down are one of the cheapest reliability wins out there.

Collapse
 
marcusykim profile image
Marcus Kim •

For the bad-migration scenario, I'd seed three markers in the drill: a valid write before the migration, the destructive change, and a valid write afterward. Then restore to before the migration and check which markers survived. That makes the tradeoff visible: removing the corruption can also remove legitimate later work. I'd put that later-write reconciliation in the recovery report, so a successful restore isn't mistaken for recovering every valid transaction.

Collapse
 
sinarezaei profile image
Sina Rezaei •

One thing I'd add to this is testing what happens after a point-in-time recovery, not just whether the database comes back.

Take PostgreSQL as an example. Suppose a bad migration drops a table at 10:15 AM. You restore to 10:14 using WAL archiving and promote the recovered instance. That recovery creates a new timeline.

Now imagine you need to investigate another issue and recover something from the original timeline. If your WAL archive retention or timeline history files aren't handled properly, you might have a much harder recovery problem than expected.

This is why I'd include multiple recovery scenarios in the drill: accidental deletion, corrupted data, and recovery after a previous PITR. A restore that works once doesn't necessarily prove that your recovery chain is reliable.

Collapse
 
paullovelace profile image
Paul Lovelace •

The green dashboard line is too real. The part I'd actually copy is restoring into an isolated env, because a test worker sending real invoices is exactly the mistake I'd make in a drill. When you ran yours, what was the missing piece — keys, roles, extensions — or did it just work first time?

Collapse
 
mudassirworks profile image
Mudassir Khan •

the pg_dump not including roles and tablespaces point is the one that bites people. we ran a restore drill last year and the dump came back fine but every grant was missing, so the app booted and then threw permission errors on half the routes. now the drill script also runs a smoke query per role. do you time the drill end to end and compare it to the RTO number, or just check that it finishes?

Collapse
 
posting-dude profile image
Posting Dude •

The pg_dump not including roles bit is exactly the kind of thing you only learn during a real incident. Step 5 is the one I'd push harder on, a restore that "worked" but the app can't log in is still a failed restore. For a solo dev with no staging, would you still do the full drill, or just restore into a throwaway local db and check a few known records?

Collapse
 
upordownonline profile image
upordown.online •

The point about restoring a backup from the normal process instead of a special export is the one people skip most. The drill only proves something if it uses the exact files you'd reach for at 3am. I'd add timing every step during the drill, because the gap between "restore finished" and "app actually serving traffic" is usually where the RTO quietly gets blown.

Collapse
 
dafili profile image
Renhua Wei •

the green dashboard is usually a size check against yesterday, and slow shrinkage
never trips it. worse: once more than half your recent runs are truncated, the
median you compare against is itself a broken backup, so it starts voting for the
failure. a hand-written floor ("below this is impossible") catches the collapse;
only comparing runs catches the drift.