Backup restore testing: a routine that proves your backups work

Backup restore testing means taking a real backup, restoring it somewhere other than production, and checking that the result is a working system with the data you expect. It matters because the failure modes of backups are silent. A job can report success while writing an empty file, skipping a database that was added later, or leaving out the folder where attachments live.

The UK National Cyber Security Centre makes the same point in plain words in its guidance on backing up your data (opens in a new tab): once you have made a backup, you should know how to restore it and check that it contains your important data. For a business application, that check has to go further than opening a file. You need the application running on the restored data.

This article sits inside our wider guide to business application hosting, where backups are one of five habits that keep a system reliable. Here we focus on the routine itself.

The recovery point objective (RPO) is the most recent data you can afford to lose, and the recovery time objective (RTO) is how long the system can be down while you restore. Every other choice, from backup frequency to storage location, follows from these two numbers.

Set them with the business owner, not the developer. Ask concrete questions. If the system went down at 3 p.m. and came back with this morning's data, what would you have to re-enter, and could you? If it stayed down until tomorrow, what would stop? The answers usually differ by system: a sales ERP may need an RPO of minutes, while an internal wiki can live with a day.

The 3-2-1 rule is a widely used rule of thumb: keep three copies of your data, on two different types of storage, with one copy off-site. For a hosted application that might mean the live database, a nightly backup on a separate volume, and a copy in object storage with a different provider or account.

The rule is a good start but was written before ransomware made attackers target backups directly. If the same administrator login can delete the server, its snapshots and the off-site bucket, the three copies are really one point of failure. The NCSC's principles for ransomware-resistant cloud backups (opens in a new tab) start with exactly this: backups should be resilient to destructive actions, for example by blocking deletion or alteration for a period after a backup is created.

In practice, add two things to 3-2-1. Make one copy immutable or delete-protected for a retention period, using the object lock or versioning features your storage offers. And keep the credentials that can delete that copy away from the production server, so a compromised application cannot reach them.

Use pg_dump when losing up to a day of data is acceptable and the database is small enough to restore within your RTO; add point-in-time recovery (PITR) when you need to restore to a specific minute. Many teams run both.

The PostgreSQL documentation on SQL dumps (opens in a new tab) explains that pg_dump produces an internally consistent snapshot of the database as it was when the dump started, without blocking normal reads and writes. Two details catch people out. pg_dump covers one database and does not include roles or tablespaces, so you also need pg_dumpall with the globals-only option to restore user accounts. And a custom-format dump (pg_dump -Fc) is restored with pg_restore, which can run in parallel to shorten restore time.

PITR works differently. PostgreSQL's continuous archiving guide describes taking a base backup, for example with pg_basebackup, and archiving every write-ahead log (WAL) file through archive_command or an archive library. To restore, you put the base backup in place, set a restore_command and a recovery target time, and PostgreSQL replays the log up to that point. It is the method that saves you when someone deletes a batch of invoices at 11:42 and you want the database as it was at 11:41.

A restorable backup includes everything needed to rebuild the running system, not only the database. The usual gaps are files stored outside the database and the configuration that ties everything together.

In Odoo, for example, attachments live in a filestore on disk, so a database dump without the filestore restores invoices with missing PDFs. Our guide to Odoo Docker deployment shows where that folder sits in a container setup. Similar patterns apply to most web applications that store uploads on disk.

• The database dump or base backup, plus archived WAL if you use PITR.
• Database roles and permissions (pg_dumpall --globals-only for PostgreSQL).
• Uploaded files and attachments, such as an Odoo filestore or a media folder.
• Application configuration files and the list of installed modules or packages with versions.
• Reverse proxy and TLS configuration, or notes on how to recreate them.
• Secrets and encryption keys, stored separately in a password manager, never next to the backup they decrypt.

A practical backup restore testing routine restores the latest backup to a clean, isolated server once a month, checks the application end to end and records the time it took. Monthly is a sensible default for most business systems; increase it after any major change to the database, the hosting or the backup job itself.

Isolation matters. The test server must not send emails, run scheduled jobs against real payment gateways or post to live integrations. Turn off outgoing mail and cron jobs on the restored copy before you start the application.

A restore is correct when the application works and the newest data matches what you expected at the backup time. Prepare the checks in advance, so the person running the test does not have to invent them under pressure.

Pick a handful of records created shortly before the backup ran, note their numbers, and look for them on the restored copy. Compare a few totals, such as the count of invoices this month or the stock value of one warehouse, against a report taken from production at the same time.

• Log in as a normal user and as an administrator.
• Find the last few documents created before the backup time.
• Open an attachment on a recent record to prove files restored.
• Run one report and compare its totals with production.
• Check that scheduled jobs are disabled on the copy.
• Note the total elapsed time from start to working application.

Most restore failures come from gaps that a single test would have exposed: missing pieces, missing access or missing knowledge. A common pattern is a backup job set up at launch that nobody revisited after a second database, a new storage folder or a version upgrade was added.

Other frequent causes are an encryption key stored only on the server that failed, a backup tool that needs a newer PostgreSQL version than the one in the restore environment, and a runbook that only one person understood. Each restore test is a chance to find these while nothing is on fire.

Record the date, the backup used, the time each stage took, what failed and what you changed. Over a few months, these backup restore testing records become evidence that recovery works and a measure of whether your RTO is realistic.

Under UK GDPR, the ICO expects organizations to be able to restore access to personal data in a timely manner after an incident. A log of successful restore tests is a practical way to show that your backup process supports that duty; confirm your specific obligations with your adviser. If you would like a team to run this routine for you, our server management plans include daily backups with periodic restore checks, and our cloud security service covers backup protection and access control. Plans and prices are on our cloud and DevOps pricing page.