0Pricing
SQL Academy · Lesson

Testing Your Restores

A backup you can't restore is useless.

Testing Your Restores is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

A Backup You Cannot Restore Is Worthless

Many teams invest time setting up automated backups but never verify whether those backups can actually be used to recover data. A backup that fails during restore is no backup at all.

This lesson walks through the discipline of restore testing: how to verify your backups work before a real disaster forces you to find out the hard way.

What Does a Restore Test Involve?

A restore test involves taking a backup file and loading it into a database — typically a separate test instance — then querying that database to confirm the data is complete and consistent.

The steps are: 1) Obtain the backup file. 2) Restore it to a sandbox environment. 3) Run validation queries. 4) Compare results against production.

Creating a Baseline Snapshot for Comparison

Before you can verify a restore, you need a baseline — a known set of counts and checksums from production that you can compare against the restored copy.

Run this on your production database and record the results:

SELECT
  'orders'        AS tbl, COUNT(*) AS row_count FROM orders
UNION ALL
SELECT
  'customers'     AS tbl, COUNT(*) AS row_count FROM customers
UNION ALL
SELECT
  'order_items'   AS tbl, COUNT(*) AS row_count FROM order_items;

Verifying Row Counts After Restore

Once the backup is restored into a test database, run the same query and compare the numbers. If the counts match, the basic structure of the restore is healthy.

A mismatch here immediately tells you data was lost during backup or restore — before you ever touch production.

-- Run on the RESTORED test database
SELECT
  'orders'        AS tbl, COUNT(*) AS row_count FROM orders
UNION ALL
SELECT
  'customers'     AS tbl, COUNT(*) AS row_count FROM customers
UNION ALL
SELECT
  'order_items'   AS tbl, COUNT(*) AS row_count FROM order_items;

Checking the Most Recent Data

Row counts confirm quantity, but they do not confirm recency. Check that the restored database contains recent records — the backup should reflect data up to the point in time it was taken.

SELECT
  MAX(created_at) AS latest_order,
  MIN(created_at) AS oldest_order,
  COUNT(*)        AS total_orders
FROM orders;

Validating Referential Integrity

Even if row counts match, a restore can leave orphaned rows — child records whose parent no longer exists. This often happens when foreign keys are not enforced during backup or restore.

Use a LEFT JOIN to detect orphaned order items:

SELECT
  oi.id       AS orphaned_item_id,
  oi.order_id AS missing_order_id
FROM order_items oi
LEFT JOIN orders o ON o.id = oi.order_id
WHERE o.id IS NULL;

Using a Checksum to Detect Corruption

For critical tables, generate a checksum of the data to detect bit-level corruption. In PostgreSQL you can combine MD5 with a cast of the entire row.

If the checksum from production and the restored copy differ, the data was altered or corrupted at some point.

SELECT
  MD5(string_agg(row_data, ',' ORDER BY row_data)) AS table_checksum
FROM (
  SELECT CAST(ROW(id, customer_id, total, created_at) AS TEXT) AS row_data
  FROM orders
) sub;

Creating a Restore Validation Table

To track the history of your restore tests, create a dedicated validation log table. Record each test run with the backup date, restored row counts, and whether validation passed.

CREATE TABLE IF NOT EXISTS restore_validation_log (
  id             SERIAL PRIMARY KEY,
  backup_taken_at TIMESTAMP NOT NULL,
  restored_at    TIMESTAMP NOT NULL DEFAULT NOW(),
  table_name     VARCHAR(100) NOT NULL,
  expected_rows  INT NOT NULL,
  actual_rows    INT NOT NULL,
  passed         BOOLEAN NOT NULL
);

Inserting a Validation Result

After each restore test, insert a row into the validation log. This gives you an audit trail proving that backups were tested and shows a pattern of pass or fail over time.

INSERT INTO restore_validation_log
  (backup_taken_at, table_name, expected_rows, actual_rows, passed)
VALUES
  ('2024-06-09 02:00:00', 'orders',      15482, 15482, TRUE),
  ('2024-06-09 02:00:00', 'customers',    8201,  8201,  TRUE),
  ('2024-06-09 02:00:00', 'order_items', 47310, 47310, TRUE);

Querying the Validation History

Regularly review the validation log to spot any regressions. A backup that passed last week but fails this week signals a problem in your backup pipeline that must be investigated immediately.

SELECT
  backup_taken_at,
  table_name,
  expected_rows,
  actual_rows,
  passed,
  CASE
    WHEN passed THEN 'OK'
    ELSE 'MISMATCH - investigate!'
  END AS status
FROM restore_validation_log
ORDER BY backup_taken_at DESC, table_name;

Point-in-Time Recovery Verification

Modern databases support Point-in-Time Recovery (PITR), which lets you restore to any moment in time using a base backup plus WAL (Write-Ahead Log) archives.

To verify PITR is working, restore to a known timestamp and check that a record created after that timestamp does NOT appear in the restored database:

-- After a PITR restore to '2024-06-09 03:00:00',
-- this order (created at 03:45) should NOT exist:
SELECT id, created_at, total
FROM orders
WHERE created_at > '2024-06-09 03:00:00'
ORDER BY created_at
LIMIT 5;
-- Zero rows = PITR worked correctly

Quick Check

Which of the following queries is most useful for detecting orphaned child records after restoring a backup?

Lesson Recap: Testing Your Restores

In this lesson you learned why restore testing is a mandatory part of any backup strategy, and how to implement it with SQL:

  • Baseline snapshots — record production row counts before testing.
  • Row count comparison — run the same query on the restored database and compare.
  • Recency check — confirm the latest records match the expected backup window.
  • Referential integrity — use LEFT JOIN to find orphaned child rows.
  • Checksum validation — detect bit-level corruption with MD5 aggregates.
  • Validation log table — track every test run for an auditable history.
  • PITR verification — confirm point-in-time recovery lands at the correct moment.

A backup is only as good as the last successful restore test. Schedule these checks regularly and treat any failure as a critical incident.

Frequently asked questions

Is the “Testing Your Restores” lesson free?

Yes — the full text of “Testing Your Restores” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Testing Your Restores”?

A backup you can't restore is useless. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Testing Your Restores” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Logical vs Physical Backups
  2. Point-in-Time Recovery
  3. Testing Your Restores
  4. Disaster Recovery Planning
← Back to SQL Academy