Skip to content
DB Tools

Database backups that actually restore

Most backup setups have never been tested. What to back up, how often, where to keep it, and the restore drill that tells you whether any of it works.

5 min read
Database backups that actually restore. An article by Athabasca Solutions.

Almost every business we look at has backups. Very few have restores.

The distinction is not pedantic. A backup is a file you hope is good. A restore is a demonstrated ability to get your business running again. Organisations discover which one they have on the worst possible day, and the discovery is not usually a happy one.

The failures we actually find

These are not hypothetical. In roughly this order of frequency:

The backup runs but contains nothing useful. A dump of an empty schema, or a container that was rebuilt so the job now points at a fresh database. The job exits zero every night, which is why nobody noticed.

Backups exist only where the data does. Same server, same account, same region. That covers disk failure and nothing else. It does not cover a deleted account, a bad migration replicated everywhere, or ransomware.

Nobody knows how to restore. The person who set it up left. The runbook is “ask Dave.” Dave works somewhere else now.

The restore takes longer than the business can survive. A twelve hour restore is a real backup and an unacceptable answer if you take orders online.

Retention is too short to matter. Seven days of backups does not help with corruption noticed after three weeks, which is the common case, because slow corruption is quiet.

What to back up

More than the database, though the database is the part people remember.

  • The data itself. A logical dump plus, for anything meaningful, continuous archiving so you can restore to a point in time rather than to last midnight.
  • The schema, separately. You want to be able to inspect structure without restoring a full copy.
  • Uploaded files. Documents, images, attachments. These usually live outside the database and get forgotten, and they are frequently the part that cannot be recreated.
  • Configuration and secrets, safely. Not in the same place as the data, but somewhere. A restored database with no idea how the application connects to it is a slower outage, not a shorter one.

Two numbers that decide everything

Before choosing tooling, write down two numbers. They convert an abstract argument into a budget.

How much data can you afford to lose? If the answer is a day, nightly dumps are fine. If the answer is five minutes, you need continuous archiving, and that is a different cost.

How long can you be down? If the answer is a day, a dump in object storage is fine. If the answer is an hour, you need a restore that has been practised and probably a standby copy.

Most small businesses, asked honestly, land on “a day of data, half a day of downtime.” That is a comfortable and inexpensive target. Some land on “an hour and an hour,” and they should know that costs meaningfully more, and decide deliberately rather than by default.

The rule worth keeping

Three copies of the data, on two kinds of storage, with one copy somewhere else entirely. It is an old rule from photography and it survives because the failure modes it covers have not changed.

For a small business running managed Postgres, that is realistically: the live database, the provider’s automated backups, and your own dump pushed to object storage in a different account. The third one is the one people skip, and it is the only one that survives your cloud account being closed or compromised.

Add immutability if you can. Object storage that refuses deletion for thirty days turns “the attacker deleted the backups” into a non-event, and it costs nothing extra.

The drill

This is the part that separates the two groups. Once a quarter, someone who is not the person who built the system restores the most recent backup into a scratch environment and answers three questions:

  1. Did it restore without errors?
  2. Is the newest record in it as recent as you expected?
  3. Can the application start against it and log someone in?

Write down how long it took. That number is your real recovery time, and it is usually two to three times what people guess.

A quarterly drill costs an hour and it is the only evidence that any of the rest is real. If it has never been done, you do not have backups, you have hope with a cron schedule.

Cheap wins

  • Alert on the absence of a backup, not just on failure. A job that stops running silently is the most common failure and the least alerted.
  • Record the size of every backup. A sudden drop is corruption or a misconfiguration, visible weeks before you would otherwise notice.
  • Encrypt before it leaves the machine, so an exposed bucket is an embarrassment rather than a breach.
  • Keep one restore document that a competent stranger could follow. Written when things are calm, not during an outage.

Where this connects to performance

Backups and performance sound unrelated and are not. A database with no maintenance, bloated tables and no index discipline takes far longer to dump and far longer to restore. Keeping the database in good shape shortens your worst day, which is a benefit nobody puts in the business case. The signs your database is the bottleneck are worth reading with that in mind.

If you are not certain your backups would restore, that uncertainty is the finding. Ask us to run the drill once and you will at least know which of the two groups you are in.

Related: database indexes explained, cleaning up a neglected database, and our database work.

Further reading

Sections covered in Database backups that actually restore: The failures we actually find, What to back up, Two numbers that decide everything, The rule worth keeping, The drill, Cheap wins, Where this connects to performance
The shape of the argument, in order.

Get new articles by email

One email when something new goes up, roughly twice a month. Plain writing on what software costs and what is worth building. No sequences, no sales calls, and one click to leave.

We use it for the newsletter and nothing else. Unsubscribe any time.

Have something you need built?

Tell us what the problem is. You will get an honest read on whether it is worth building, what it would take, and roughly what it would cost. No pitch deck, no pressure.

Replies within one business day. Mon to Fri, 9am to 5pm MT.

Call Start a project