A database backup checklist that proves you can restore

Almost every team has backups. Far fewer have restores, and the difference is only discovered on the worst day. This checklist is arranged in the order you would build it: decide what you need, protect it, then prove it works.

Start with two numbers

Without targets you cannot tell whether a backup is good enough.

• Recovery point objective (RPO): how much recent data you can afford to lose, measured in time. An RPO of 24 hours means a nightly backup is enough. An RPO of five minutes means you need continuous log shipping, not a nightly dump.
• Recovery time objective (RTO): how long you can be without the system. An RTO of four hours means a restore that takes six is a failure, however good the backup.

Ask the people who depend on the data, not only the people who run the database. The answers usually cost money, and that is the point of asking: the numbers turn a vague wish ("don't lose data") into something you can design for and test against.

What each item protects you from

The checks fall into four groups, and a gap in any of them is how recoveries fail in practice.

Know the target. The two numbers above, written down and agreed.

Back up the right things, in the right way. A copy that cannot be restored in a consistent state, or that is missing the keys, roles or configuration, looks fine until it is needed. Take backups with the database's own tools or a proper snapshot, never by copying the files of a running database.

Keep it safe. Backups are a target for attackers and a victim of the same failures as the system they protect. Separate copies, offsite storage, encryption and one immutable copy mean one incident cannot take both.

Prove it works. This is the group most often skipped. A restore test is the only evidence that everything above was done correctly, and it gives you the real recovery time instead of an estimate.

Taking a consistent backup

Every database has a supported way, and it is almost always better than copying files. In SQLite, Python's sqlite3 module exposes the online backup API, which copies a database that is in use. This script takes a backup and then checks it, which is the habit to copy:

import os
import sqlite3
import tempfile

folder = tempfile.mkdtemp()
live = sqlite3.connect(os.path.join(folder, "app.db"))
live.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, total REAL)")
live.executemany("INSERT INTO orders (total) VALUES (?)", [(10.0,), (25.5,), (7.25,)])
live.commit()

# 1. take a consistent copy while the database is in use
backup_path = os.path.join(folder, "app-backup.db")
copy = sqlite3.connect(backup_path)
with copy:
    live.backup(copy)
copy.close()

# 2. prove it can be restored: open the copy as if it were a new server and check it
restored = sqlite3.connect(backup_path)
print("integrity:", restored.execute("PRAGMA integrity_check").fetchone()[0])
print("rows:", restored.execute("SELECT COUNT(*) FROM orders").fetchone()[0], "| newest id:", restored.execute("SELECT MAX(id) FROM orders").fetchone()[0])
print("matches live:", restored.execute("SELECT SUM(total) FROM orders").fetchone() == live.execute("SELECT SUM(total) FROM orders").fetchone())

integrity: ok
rows: 3 | newest id: 3
matches live: True

The second half is the important part. A backup file that exists says nothing; one that opens, passes an integrity check and matches the live data is evidence.

For PostgreSQL the equivalent tools are pg_dump (a logical backup of one database) with pg_restore to load it, and continuous archiving of the write-ahead log for point-in-time recovery. A common pair of commands is:

pg_dump --format=custom --file=app.dump appdb
pg_restore --list app.dump        # reads the dump and lists its contents: a quick sign it is not corrupt

A restore drill

Pick a quiet hour and rehearse the real thing:

1. Restore last night's backup into an empty environment that is not production.
2. Start the application against it.
3. Check: row counts against the source, the newest record's date, a handful of real queries, and that people can sign in.
4. Time the whole exercise from "go" to "working". That is your real RTO.
5. Write down every step that was unclear or missing, fix the runbook, and book the next drill.

Using the list

Treat it as a review, not a one-off. For each line write down the current answer, who owns it and when it was last checked. The lines you cannot answer are your next tasks, and the order to tackle them is the order the list is in. The drill is the best test of the whole list at once: whatever it exposes is what to fix first.