How to verify a database backup

Take the copy with the database's own command, then prove it by reading it: sqlite3 live.db ".backup backup.db", then PRAGMA integrity_check, a row count and a checksum against the source. The backup exited 0 in silence; the three reads returned ok, 50000 rows and a digest identical to the source.

Why check this

A backup is verified before you need it, or it is not verified. This belongs in the release checklist beside the migration steps, and in the job that takes the nightly copy, because an unread backup is a file, not a recovery plan.

The failure it catches is a copy that looks fine. In the run below, a plain cp of a live database exited 0, produced a 20107264 byte file, and threw database disk image is malformed the first time anything read a row. Nothing warned at copy time.

Prerequisites

• The sqlite3 3.50.6 shell. On this machine that binary is the Android platform-tools build, the only sqlite3 on PATH, so every shell result below is named as its output. Each was confirmed a second time through node:sqlite, which carries SQLite 3.51.3, and both engines agreed.
• SQLite is the only database engine on the verifying machine. The argument transfers to any engine; the commands do not. .backup and .dump are SQLite spellings, documented in the CLI reference.
• The fixture, 50000 rows in live.db:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer_email TEXT NOT NULL, status TEXT NOT NULL,
  amount_cents INTEGER NOT NULL, created_at TEXT NOT NULL);
INSERT INTO orders (id, customer_email, status, amount_cents, created_at)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 50000)
SELECT i, 'user' || (i % 900) || '@example.com',
       CASE WHEN i % 17 = 0 THEN 'refunded' WHEN i % 7 = 0 THEN 'cancelled'
            WHEN i % 3 = 0 THEN 'shipped'  WHEN i % 2 = 0 THEN 'pending'
            ELSE 'paid' END,
       (i * 37) % 90000 + 500,
       date('2026-01-01', '+' || (i % 240) || ' days')
FROM n;

• The writer that steps 4 to 6 copy underneath. It stages 400000 rows in one transaction, holds it open for 30 seconds, then rolls back, so nothing it does is ever committed. Run it with node writer.mjs in a second shell:

import { DatabaseSync } from 'node:sqlite';
import { writeFileSync } from 'node:fs';

const db = new DatabaseSync('live.db');
db.exec('PRAGMA journal_mode = DELETE');
db.exec('BEGIN IMMEDIATE');
const ins = db.prepare(
  'INSERT INTO orders (customer_email, status, amount_cents, created_at) VALUES (?, ?, ?, ?)'
);
for (let i = 0; i < 400000; i += 1) {
  ins.run('bulk@example.com', 'pending', 100 + i, '2026-06-01');
}
writeFileSync('staged.flag', 'open');
console.log('400000 rows staged, transaction still open');
setTimeout(() => { db.exec('ROLLBACK'); db.close(); console.log('rolled back'); }, 30000);

Steps

1. 1

   Step 1.

   Take the backup through the database, not through the filesystem.

   sqlite3 live.db ".backup backup.db"; echo "exit=$?"
   

   exit=0

   Silence and a zero. That is the entire report, and it is worth nothing until something reads the file.

2. 2

   Step 2.

   Open the backup and ask the engine whether its pages hang together.

   sqlite3 backup.db "PRAGMA integrity_check;"
   

   ok

   ok means the b-trees, page links and indexes are consistent. It says nothing about whether your rows are there.

3. 3

   Step 3.

   Count and hash both files with one loop, so the two numbers come from the same command and cannot drift apart.

   for db in live.db backup.db; do printf '%s rows=%s sha=' "$db" "$(sqlite3 "$db" 'SELECT count(*) FROM orders;')"; sqlite3 -noheader -list "$db" "SELECT quote(id)||'|'||quote(customer_email)||'|'||quote(status)||'|'||quote(amount_cents)||'|'||quote(created_at) FROM orders ORDER BY id;" | sha256sum; done
   

   live.db rows=50000 sha=5576e6ba7f6e5cb401c1f66dfe89c0a7b5aab51d4348aeecbbe92c29d5f996e3 *-
   backup.db rows=50000 sha=5576e6ba7f6e5cb401c1f66dfe89c0a7b5aab51d4348aeecbbe92c29d5f996e3 *-

   Same count, same digest. Three reads of the file, and only now is the backup verified.

4. 4

   Step 4.

   Start the writer from Prerequisites in a second shell and, while its transaction is open, take the backup again.

   sqlite3 live.db ".timeout 5000" ".backup hot-backup.db"; echo "exit=$?"
   

   exit=1
   Error: database is locked

   After waiting five seconds it refused. The writer holds the file while it spills 400000 staged rows into it, and the backup declined to copy a half written database. It still created hot-backup.db, zero bytes long.

5. 5

   Step 5.

   At the same moment, take the copy the ordinary way, then read one row out of it.

   cp live.db copy-cp.db && sqlite3 copy-cp.db "SELECT count(*) FROM orders;"
   

   Error: stepping, database disk image is malformed (11)

   cp exited 0 and wrote 20107264 bytes, against 2531328 for the source. It captured pages from a transaction that was later rolled back, without the journal that would undo them.

6. 6

   Step 6.

   Ask that copy the same question step 2 asked.

   sqlite3 copy-cp.db "PRAGMA integrity_check;" | head -4
   

   *** in database main ***
   Tree 3 page 482 right child: invalid page number 824
   Tree 3 page 482 right child: invalid page number 823
   Tree 3 page 482 right child: invalid page number 821

   The full output is 101 lines, one header and 100 errors, which is the default cap rather than the true total. PRAGMA integrity_check(500) on the same file printed 207 lines before failing outright.

7. 7

   Step 7.

   Now test the other direction. sqlite3 live.db ".dump" > dump.sql wrote 50010 lines; head -25000 dump.sql > truncated.sql cut it short, the way a full disk would.

   sqlite3 restored.db < truncated.sql; echo "exit=$?"
   

   exit=0

   The file stops in the middle of the inserts, before the COMMIT. The restore reported success.

8. 8

   Step 8.

   Read the restored database rather than trusting that zero.

   sqlite3 restored.db "SELECT count(*) FROM orders;"
   

   Error: in prepare, no such table: orders

   restored.db is 0 bytes. A .dump opens with BEGIN TRANSACTION, the truncated file never reached COMMIT, and closing the connection rolled the whole thing back. PRAGMA integrity_check answers ok on that empty file.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | ok, matching count, matching digest | The backup is readable and complete | Record the digest and the row count with the backup file. | | ok and a count of 0 | The file is sound and empty | Look at the restore log. A rolled back transaction leaves exactly this. | | Tree N page N right child lines | Page links are broken | Discard the copy. It was taken while the file was being written. | | database disk image is malformed | The header parsed, the pages did not | Same verdict. Take a new copy through the database. | | Error: database is locked | A writer held the file | Retry with a longer .timeout, or take the backup from a replica. This is the safe failure. | | A backup file of 0 bytes | The command created the target and then failed | Check the exit code. Existence of the file is not evidence of anything. |

Common mistakes

What to check next

• How to check database integrity: what integrity_check covers, and what it leaves to you.
• How to verify row counts after migration: the count half of step 3, and why it is never enough alone.
• How to check data integrity after migration: the digest half, including why the same rows can hash differently.
• How to check database locks: the lock that made step 4 fail, and how to take a backup around it.

FAQ

How do I verify a database backup?

Restore it or open it, then read three things: PRAGMA integrity_check, the row count of every table, and a checksum of the ordered rows compared against the source. A backup nobody has read is untested.

Is copying the database file a backup?

Only while nothing is writing. With a writer active, the copy in step 5 was malformed and cp reported success. Use the engine's own backup command, which takes a consistent snapshot or fails loudly.

How often should a restore be tested?

Every time the backup procedure changes, and on a schedule matching how much data you can afford to lose. The test that counts is a restore into an empty database, read by the application.

Can I verify a backup without restoring it?

Partly. integrity_check reads the file without a restore. It cannot tell you that all your rows are present, which is why step 3 counts and hashes instead of trusting ok.

Verified