Test Your Backups: A Restore Drill for Any Backend

Test your backups by restoring one into an isolated target, checking business-critical records, and timing how long it takes to make a safe copy of the app work. A green backup status proves that a backup job ran. It does not prove the right data was captured, the restore is usable, or the rest of the app can reconnect.

A restore drill is not a full disaster recovery drill. The restore drill proves the data comes back. A DR drill proves the whole business can run somewhere else: people, DNS, payments, support. Start with the restore drill, because it is the cheap half and the half that is almost never tested.

If data has already disappeared, a first-hour runbook for an accidentally deleted production database covers the next sixty minutes. Avoid running a practice procedure against the only remaining copy during an active incident.

What does a restore drill prove that a backup status cannot?

A restore drill proves four facts: a recovery point exists, the backup contains the records you need, the restored data can support the app, and the elapsed recovery time is tolerable. It also exposes dependencies that a database backup never claimed to include, such as object-storage files, secrets, functions, scheduled jobs, webhooks, and authentication settings.

The distinction matters because managed platforms use the word “backup” for different artifacts. Supabase documents that its database restore does not copy Storage objects and that restoring to a new project still requires manual reconfiguration of functions, API keys, Realtime settings, and other project features. Firestore likewise documents that a managed export is not an exact snapshot taken at the start of the export.

A backup is not proven usable until a restore shows what comes back and what remains missing.

How do you check a backup file before you restore it?

Check the archive before you spend an afternoon on a restore. Three cheap checks catch most bad backups: confirm the file is readable and contains the tables you expect, compare its size against the previous run, and record a checksum so you can prove later that the file you tested is the file you kept.

pg_restore --list prints the table of contents of a custom-format archive without touching a database. PostgreSQL documents the option as “List the table of contents of the archive.” If that command errors, the file is truncated, corrupt, or written by a newer pg_dump than the pg_restore you are running.

# Backup integrity check: read the archive, count the tables, checksum the file
pg_restore --list backup.dump | head -40
pg_restore --list backup.dump | grep -c "TABLE DATA"
ls -l backup.dump
shasum -a 256 backup.dump | tee backup.dump.sha256

A size drop against the previous run is the most reliable early warning you get for free. A dump that halves overnight usually means a partial dump, a permissions change that skipped a schema, or a table that quietly stopped being included. Compare sizes on every run, not only on drill day.

On a managed platform where you never see a file, the equivalent check is confirming a backup exists for the date you expect and that its reported size or document count sits in the normal range.

This is the cheapest rung on a ladder worth naming, because most owners are standing below all of it:

1. Read the archive and compare its size to the last run. Minutes, and it can run weekly.
2. Restore into a scratch database and run a handful of verification queries. Under an hour once scripted.
3. Full drill: restore, connect a disposable app, exercise a real flow, time the whole thing.
4. Scheduled restore testing that repeats rung 2 or 3 on a cadence and alerts when it fails.

Rung 1 alone catches the silent partial-dump failure, which is the failure most likely to be waiting in a backup nobody has opened.

How do you test a backup restore without risking production?

Test a backup restore in a new, isolated database or project with outbound side effects disabled. Never point production at the restored target merely to see whether it works. Use a disposable app deployment, block email and payment delivery, and give the restored environment different credentials so a test cannot affect customers.

Sysadmins call this restoring to an alternate location. Managed platforms call it restore to a new project or a new instance. Same idea: the restore lands somewhere that is not production, and nothing in production changes because of it.

Before starting, name the failure you are simulating: a bad migration, a deleted table, a wiped project, a compromised key. The scenario decides which recovery point you pick and which records you check. A drill with no named scenario tends to check whatever happens to be easy.

Then choose a recovery point old enough to be clearly before a known harmless change. Record the expected value of a few critical rows, the number of records in important tables or collections, and the presence of a recent user, order, or other business object. Those checks are more informative than “the restore completed.”

What counts as a pass

Write the pass criteria down before you start, so the drill is a test rather than a judgement call afterwards. A drill passes when all five of these hold:

• The restore lands within an agreed margin of the chosen recovery point.
• Every named critical record is present and correct.
• Login plus one write flow completes in the restored copy with side effects disabled.
• Elapsed time is inside the business limit.
• Every excluded system is written down.

Anything else is a fail with a known cause, which is still a good drill. The point of writing the criteria first is that a half-working restore stops feeling like a pass.

1. 01 Write down the recovery point, its timezone, and three to five records or totals that must exist at that point.
2. 02 Create or select an isolated restore target. Keep production connected to its current database and disable outbound email, webhooks, billing, and scheduled jobs in the test environment.
3. 03 Start a timer, restore the selected backup, and save the provider job id plus every warning or error.
4. 04 Confirm the restore reached the intended timestamp. Check critical rows, record counts, relationships, recent migrations, and authentication records where the platform includes them.
5. 05 Connect a disposable copy of the app with test-only secrets. Exercise login and one important read and write flow without contacting real users or payment systems.
6. 06 List everything outside the restore: uploaded files, secrets, functions, cron jobs, API keys, provider settings, custom domains, or third-party data.
7. 07 Record the elapsed time, manual steps, access needed, and the person who could repeat the procedure from the written record.
8. 08 Securely decommission the restored copy under your retention policy after verification, or keep it access-restricted for an approved reason. It contains production-derived data and must not become an unmanaged duplicate.

For a self-managed PostgreSQL export, the documented custom archive path uses pg_dump and pg_restore. Run it only against an empty scratch database you have identified explicitly:

pg_dump --format=custom --file=backup.dump "$SOURCE_DATABASE_URL"
pg_restore --exit-on-error --no-owner --dbname="$SCRATCH_DATABASE_URL" backup.dump

PostgreSQL’s pg_restore documentation explains that custom archives come from pg_dump, --no-owner suppresses ownership-setting commands, and --exit-on-error stops at the first SQL error instead of continuing with a partly failed restore. Use only an archive from a database whose source administrators you trust because a restore executes code stored in the archive. I have deliberately left out --clean: that option issues DROP commands and is an unnecessary hazard in a general drill. Provider-managed physical backups should be restored through the provider’s documented workflow instead of being treated like logical dump files.

How should you verify the restored data before declaring success?

Verify both data integrity and application behavior. A successful provider job can still leave the app unusable because an extension is missing, a secret points at production, an object-storage file was never in the database backup, or the authentication and database state came from different recovery points.

Use checks that reflect how the business uses the data:

Do not compare every count blindly against current production. Production may have accepted legitimate writes after the recovery point. Compare the restored state with evidence captured for that timestamp, or with expectations that account for the time difference.

Why did the restore fail?

A restore that errors is the drill doing its job, and the errors are boring once you have seen them. These are the ones this reader hits, with the cause and the fix.

Record the exact error text in the drill notes even after you fix it. The second drill is short precisely because the first one wrote down which errors to expect and who holds the access needed to clear them.

Where does the restore process differ across common backends?

The safe pattern stays consistent across providers, but the restore target and missing pieces differ. Check the current product documentation immediately before a drill because plan entitlements and recovery workflows change.

Two details the table cannot hold. Supabase’s Restore to a New Project is limited to paid projects with physical backups enabled and produces a database-only copy. Firestore’s managed export and import also require billing, incur document operations, and an export may include writes made while the export was running, so it is not an exact snapshot of the moment it started.

Cost, since that is the usual blocker. Supabase’s Restore to a New Project needs a paid project with physical backups enabled, so the drill costs at least one month of a paid plan. Firestore managed backups and managed exports need the Blaze pay-as-you-go plan, and exports bill document operations, so a large collection is not free to export. An Amazon RDS point-in-time restore bills a second instance for as long as that instance exists, which is usually an hour or two. A self-managed pg_dump and restore costs disk and time only. In every case the restored copy can be destroyed the same day.

For its current retention and database-versus-storage boundary, the Supabase backup guide covers it in full once it’s live. The primary references for this drill are the current Supabase backup documentation, Supabase Restore to a New Project documentation, Firestore backup and restore documentation, Firestore export and import documentation, and Amazon RDS point-in-time restore documentation.

Is your backup actually a separate copy?

A backup governed by the same project controls is not a separate copy. Supabase states its own deletion boundary plainly in its backup documentation: “When you delete a project, we permanently remove all associated data, including any backups stored in S3. This action is irreversible.”

The realistic threat for a small app is not a fire in a data center. It is a runtime key leaked in a client bundle, a contractor who still has admin access, or an AI agent running a destructive command with account credentials. Those credentials do not have the same reach. Supabase secret and service_role keys provide elevated access to project data. Project management actions use a personal access token or scoped OAuth token. Firestore draws a different boundary again: deleting the source database does not automatically delete its backups.

A leaked runtime key can still erase application data. It does not by itself show that the attacker can delete provider-managed backups in the same action. Check the provider’s credential and backup boundaries separately.

Three controls, cheapest first:

• Keep at least one copy outside the platform. That is the plain-English core of the 3-2-1 rule: more than one copy, more than one kind of storage, at least one somewhere else. A scheduled pg_dump written to storage in a different account clears the bar.
• Separate the credentials. The app’s runtime key should not be the PAT, OAuth token, IAM role, or account credential that can change retention, delete backups, or delete the project.
• Use retention locks or write-once storage where the provider offers them, so a stolen key cannot shorten retention or delete objects.

Supabase’s documentation tells Free plan projects to export their own data regularly with the CLI db dump command, since daily backups are a paid-plan feature. If that is your setup, the copy you take yourself is not a second copy. It is the only copy, and it deserves the drill more than anyone else’s.

What recovery time and recovery point objectives should a small app choose?

A recovery time objective, or RTO, is the longest outage the business has decided it can tolerate. The measured drill time tells you whether the current process can meet that decision. There is no responsible universal target for a small app because an hour of downtime has different consequences for an internal tracker, a paid booking system, and a clinical workflow.

The paired number is the recovery point objective, or RPO: how much recent work the business can afford to lose. It is set by the backup schedule, not by the drill. A nightly backup implies an RPO of up to 24 hours, so a failure at 23:00 loses nearly a full day of writes. Point-in-time recovery shrinks that window to minutes. Supabase’s production checklist puts it directly: “If you need a lower recovery point objective (RPO), enable Point-in-Time Recovery (PITR).” The drill measures RTO, the schedule sets RPO, and improving one does nothing for the other.

Record two times separately: how long the database restore takes, and how long the complete business flow takes to return. The second figure includes configuration, credentials, verification, traffic switching, and any object-storage or third-party recovery. A fast database restore can sit inside a slow service recovery.

If the measured time exceeds the business limit, shorten a specific step. That may mean a smaller recovery scope, more frequent recovery points, a written credential map, automated validation queries, or a prebuilt isolated environment. The drill supplies the evidence for that decision.

Can you automate the restore drill?

Yes, in part, and the automated version has a name: restore testing. The manual drill proves recovery works once. Scheduled restore testing proves it still works after the schema, the plan, and the people have changed. AWS Backup ships this as a product feature, and four things it does are worth copying whatever your stack is.

Put it on a schedule. A restore test on a calendar happens. One that depends on somebody remembering does not.

Test an old recovery point, not just yesterday’s. AWS Backup lets a testing plan pick either the latest recovery point in a window or a random one, and the documentation says a random pick is there “to gauge the general health of recovery points.” The oldest backup you still rely on is the one most likely to be quietly broken.

Give the restored copy a short life. AWS Backup keeps a restored resource for a retention period you set, from 1 to 168 hours, so validation can run, then deletes it automatically. Copy the pattern by hand: restore, verify, destroy, on a deadline, so a test copy of production data never becomes a permanent unmanaged duplicate.

Record the duration every run. AWS Backup Audit Manager has a control for whether restore time meets a target. A restore that has crept from 8 minutes to 40 is something you want to see on a chart, not discover during an incident.

You do not need AWS to get most of this. A weekly job that runs pg_restore --list against the newest dump and alerts on failure is rung 1, automated. A monthly job that restores into a throwaway database, runs a few verification queries, prints the elapsed time, and drops the database again is most of the value of the full drill for a few minutes of attention.

How often should you run a backup restore drill?

Run one full drill before the app becomes difficult to take offline, then repeat it on an interval that follows the rate of change and the consequence of downtime.

Three changes override the calendar. Run a drill early when any of them happens:

• The platform or plan changes, including a plan downgrade that shortens retention.
• A schema or storage migration changes what actually gets backed up.
• The people holding the credentials change.

Keep the next drill short by saving the exact recovery point format, verification queries, missing-dependency list, timing, and cleanup steps. The restore record also belongs beside the one-database, no-staging deployment risk, because both controls depend on having somewhere isolated to test a consequential change.

Why founders skip the restore drill

The reasons are always the same four, and each has a short answer.

For context on how common the gap is: in the June and July 2026 AxonBuild research corpus, Data Integrity & Safety averaged 51.6 out of 100 across the 20 third-party apps where that area could be scored. The corpus records missing backups, missing staging, and destructive paths, like the leftover maintenance endpoint that would drop every table in one request, but it does not count how many owners had completed a restore drill. The 51.6 figure provides data-safety context, not a measured restore-test rate.

There is one more reason to run the drill that has nothing to do with losing data. Enterprise buyers, investors, and vendor security questionnaires ask whether backups are tested. A dated drill record with the elapsed time, the verification queries, and the list of excluded systems answers that in one line. A green backup toggle does not.

Backup recovery is one part of deciding whether an AI-built app is ready to launch. A readiness decision still depends on the restore evidence recorded during a real drill.

Common questions about testing backups

How do you actually test a backup?

You test a backup by restoring it somewhere that is not production and then checking that the data is right. Restore into a new database, project, or instance, confirm the records you named beforehand are present, connect a disposable copy of the app with side effects disabled, and time the whole thing. Anything short of a completed restore is a backup status, not a test.

Does enabling backups mean I can restore my app?

Enabling backups confirms that a backup feature is configured. A complete app restore also depends on the recovery window, captured data, credentials, files, functions, provider settings, and a verified traffic-switch procedure. Test those parts in isolation before relying on the status indicator.

Should a restore drill ever overwrite the production database?

No. A planned drill should restore into a separate target whenever the platform supports it. An in-place restore can replace newer production data and create avoidable downtime. If a provider only offers in-place restoration, use a logical export into an isolated environment or ask the provider for a safe test method.

How do I know whether a restored backup is complete?

A restored backup is complete for your recovery objective when critical records match the chosen point, the app’s important flow works with side effects disabled, and every excluded system is documented. A provider success message alone cannot establish those facts.

How often should I test my backups?

Run a full restore drill once a year for an internal tool, quarterly for a paid app holding customer data, and monthly for regulated or clinical data. Override the calendar and drill early whenever the platform or plan changes, a migration changes what gets backed up, or the people holding the credentials change. The cheap checks, reading the archive and comparing its size to the previous run, can run weekly and automatically.

What is the difference between RTO and RPO?

RTO, the recovery time objective, is how long the business can be down. RPO, the recovery point objective, is how much recent work it can afford to lose. The drill measures RTO; the backup schedule sets RPO. A nightly backup puts up to 24 hours of writes at risk, while point-in-time recovery reduces that window to minutes.

Is point-in-time recovery the same as a normal backup?

Point-in-time recovery uses a base backup plus a history of changes to reconstruct the database near a chosen moment. A daily snapshot gives fewer recovery points. Both still need a restore drill, and neither automatically includes every service surrounding the database.

What do I do if the restore itself fails?

Read the error text before changing anything, because most restore failures are boring and fixable. A version mismatch in the file header means your pg_restore is older than the pg_dump that wrote the archive, ownership and role errors are the reason the drill uses --no-owner, and a missing extension means the scratch server was not built like production. Record the exact error in the drill notes so the next drill starts with the fix already known.

Does restore testing cost money?

Usually a little. Supabase’s Restore to a New Project needs a paid project with physical backups enabled, Firestore managed backups and exports need the Blaze plan and bill document operations, and an Amazon RDS point-in-time restore bills a second instance for as long as it runs. A self-managed pg_dump into a scratch database costs only disk and time, and every restored copy can be destroyed the same day.

Can I test a backup without a paid plan?

Yes, as long as you drill the copy you take yourself instead of the platform’s restore button. Dump the database with pg_dump or the vendor’s export command, restore that file into an empty local or throwaway database, run the verification queries you wrote down, and time it. Supabase’s Free plan has no daily backups, and its documentation tells Free projects to export their own data regularly with the CLI db dump command and keep off-site copies, so that export is the artifact to drill. What the free tier cannot give you is the platform’s own restore path, since Supabase documents Restore to a New Project as available only to paid projects with physical backups enabled. On a free plan the drill matters more, not less: the export is the only copy you have.