Your Backup Isn't Real Until You Restore It to a Temp Database and Check

Backup scripts are scheduled, monitoring dashboards stay green, and the backup directory gets a fresh file every day — it all looks reassuring. But the day you actually need it — someone accidentally drops a table, or a machine fails and you have to restore to a new one — many people discover for the first time: the backup won't restore. The file is empty, some objects are missing, versions don't match — all kinds of things can go wrong.

Between a backup that "ran successfully" and one that "actually works" lies something almost nobody proactively does: restore it and check. This article does exactly that — restores a custom-format backup to a temporary database, then verifies it item by item: is the data correct, are all objects present, are constraints still in place, do business queries run correctly. The sys_dump and sys_restore parameters covered in Part 1 won't be repeated here; the focus is a verification methodology.

As usual, a note on accounts: this involves creating and dropping databases — DBA work — so we connect as system. For day-to-day development queries and modifications, a regular user is sufficient; don't reach for the admin account by default. The demo database is called backup_src_db, with three tables following a typical product–customer–order schema, including primary keys, foreign keys, unique constraints, check constraints, indexes, and a view. The data also contains Chinese characters.

First, capture a baseline from the source database

Before backing up, query a set of data from the source database and record it. Note: the point is not to check "do the tables exist," but to query a few values that are meaningful from a business perspective:

ksql -h 127.0.0.1 -p 54321 -U system -d backup_src_db -c "
select
  count(*) as order_count,
  sum(total_amount) as amount_sum,
  max(created_at) as last_created_at
from t_bak_order;"

ksql -h 127.0.0.1 -p 54321 -U system -d backup_src_db -c "
select * from v_bak_customer_amount order by customer_id;"

The order table has 5 rows, total amount 1235.10, last order at 2026-06-30 09:50:00. The view summarizes by customer: Zhang San 2 orders totaling 288.50, Li Si 2 orders totaling 880.00, Wang Wu 1 order totaling 66.60.

Why not just select count(*) and be done? Because matching row counts doesn't mean the data hasn't shifted. One missing amount value, or fields out of order — row count alone won't catch that. Metrics like total amount and the timestamp of the last record embed concrete values, making them reliable for reconciliation after restore. This set of numbers becomes the "answer key" for the verification that follows.

Back up, then don't rush to restore

Back up in custom format, placing the file in a date-stamped directory:

BACKUP_DIR=/acowbo/kingbase/backups/verify_20260630
mkdir -p "$BACKUP_DIR"

sys_dump -h 127.0.0.1 -p 54321 -U system -d backup_src_db -F c -f "$BACKUP_DIR/backup_src_db.dump"
ls -lh "$BACKUP_DIR/backup_src_db.dump"

sys_restore -l "$BACKUP_DIR/backup_src_db.dump" | sed -n '1,80p'

The file is 6.7K, sitting there. But the file's existence is only the first layer — whether its contents are complete requires a look at the archive listing with sys_restore -l.

The output header shows the archive's identity: dbname: backup_src_db, TOC Entries: 20, Format: CUSTOM, exported from a 12.1 database. Below, the Selected TOC Entries list every object inside: the wmsys schema (KES built-in), two tables t_bak_customer and t_bak_order, sequences, the view v_bak_customer_amount, table data, various constraints, the idx_bak_order_customer_created index, and the foreign key t_bak_order_customer_id_fkey. All expected objects are in the manifest.

The significance of this step: before even attempting a restore, you can confirm the backup isn't an empty shell — 20 TOC entries, table data and constraints all listed, meaning the archive itself is at least structurally complete. Custom format allows listing the table of contents like this; with plain-text backups, you'd have to grep through it yourself.

Restore to a temporary database — don't touch the source

A critical point: restore to a newly created temporary database, never onto the source database. Name the temporary database something obvious so its purpose is clear at a glance:

ksql -h 127.0.0.1 -p 54321 -U system -d app_db -c "drop database if exists backup_verify_db;"
ksql -h 127.0.0.1 -p 54321 -U system -d app_db -c "create database backup_verify_db;"

sys_restore -h 127.0.0.1 -p 54321 -U system -d backup_verify_db -v "$BACKUP_DIR/backup_src_db.dump"

-v makes sys_restore print every step: connecting, creating the wmsys schema, creating the two tables, creating sequences, creating the view, loading data — here you can see finish restoring contents of table "public.t_bak_customer" 3 rows, t_bak_order 5 rows, row counts matching the source database; then creating primary keys, unique constraints, indexes, and finally the foreign key t_bak_order_customer_id_fkey. The entire restore process completes step by step without interruption.

However, -v finishing without errors only means "the restore command succeeded," not "the data matches the source." Command success is a necessary condition, not a sufficient one — you still need to check for yourself below.

Is the data correct? Compare both sides

Run the same aggregate queries on both the source and temporary databases, also selecting the database name, and place them side by side:

ksql -h 127.0.0.1 -p 54321 -U system -d backup_src_db -c "
select 'backup_src_db' as db_name,
  count(*) as order_count, sum(total_amount) as amount_sum, max(created_at) as last_created_at
from t_bak_order;"

ksql -h 127.0.0.1 -p 54321 -U system -d backup_verify_db -c "
select 'backup_verify_db' as db_name,
  count(*) as order_count, sum(total_amount) as amount_sum, max(created_at) as last_created_at
from t_bak_order;"

Both sides show 5 / 1235.10 / 2026-06-30 09:50:00, identical to the baseline captured before backup. At this point, the data check is passed — not because the command didn't error, but because these business metrics actually match.

Objects, constraints, indexes — not a single one can be missing

Data matches, now check the structure. Backup and restore is never just about a few rows of data; constraints, indexes, and views are all part of the restore result. First, look at tables and views:

ksql -h 127.0.0.1 -p 54321 -U system -d backup_verify_db -c "\dt"
ksql -h 127.0.0.1 -p 54321 -U system -d backup_verify_db -c "\dv"

\dt shows both tables t_bak_customer and t_bak_order present. \dv lists three views — don't be alarmed by the two extra ones: sys_stat_statements and sys_stat_statements_all are KES built-in statistics views unrelated to this database's backup. The one you're looking for is v_bak_customer_amount, and it's there.

Next, pull the constraints and index definitions for the order table:

ksql -h 127.0.0.1 -p 54321 -U system -d backup_verify_db -c "
select c.conname, c.contype, _get_constraintdef(c.oid) as constraint_def
from _constraint c
where c.conrelid = 'public.t_bak_order'::regclass
order by c.conname;"

ksql -h 127.0.0.1 -p 54321 -U system -d backup_verify_db -c "
select indexname, indexdef from _indexes
where schemaname = 'public' and tablename = 't_bak_order'
order by indexname;"

All five constraints on the order table are back: the foreign key customer_id referencing the customer table, the unique constraint on order_no, the check constraint limiting order_status to paid/pending/refund, the check constraint total_amount > 0, and the primary key. The index is also present, including the composite index on (customer_id, created_at desc).

Skipping this step is an easy trap to fall into: if a restore brings back data but not constraints, the database looks fine at first, but later when the application writes dirty data, foreign keys and checks won't block it — the problem gets buried. So whether constraints are present must be confirmed explicitly; you can't assume they came back with the data.

Run business queries, then deliberately violate a constraint

"Being able to query" isn't enough — the data needs to actually participate in business logic. First, run the view:

ksql -h 127.0.0.1 -p 54321 -U system -d backup_verify_db -c "
select * from v_bak_customer_amount order by customer_id;"

The view calculates Zhang San 288.50, Li Si 880.00, Wang Wu 66.60 — completely consistent with the source database baseline — confirming that the view definition, the underlying joins, and aggregations all work correctly.

Then deliberately violate a constraint by inserting an order referencing a non-existent customer:

ksql -h 127.0.0.1 -p 54321 -U system -d backup_verify_db -c "
insert into t_bak_order(order_no, customer_id, total_amount, order_status, created_at)
values ('BKO-BAD-001', 999, 10.00, 'paid', timestamp '2026-06-30 10:00:00');"

The foreign key immediately rejects it:

ERROR:  insert or update on table "t_bak_order" violates foreign key constraint "t_bak_order_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "t_bak_customer".

Only this step truly validates the constraint. Earlier, querying _constraint confirmed the constraint definition exists — that only proves "the definition is there." Here, inserting illegal data and having it blocked proves the constraint is "actually working." These are not the same thing — cases where a definition exists but is somehow not in effect do happen.

The backup file itself must be traceable

Finally, record a set of file-level information:

ls -lh "$BACKUP_DIR/backup_src_db.dump"
sha256sum "$BACKUP_DIR/backup_src_db.dump"

File size can reveal anomalies at a glance — a backup that suddenly appears as 0 bytes or suspiciously small likely indicates something went wrong. The use of the sha256 hash needs to be stated precisely: it allows verifying whether the file has been altered or corrupted after being transferred offsite or archived for some time. But it only proves "this file's bytes are still the same bytes" — it does not prove "this database can be restored." Whether it can be restored is confirmed by the earlier steps: restoring to a temporary database and verifying data and objects item by item. Hashing and restore verification are two separate things; don't mistake a matching hash for a usable backup.

What this process actually verified

Looking back: a baseline was captured before backup; after backup, sys_restore -l confirmed the archive wasn't an empty shell; restore was to a temporary database, never touching the source; data was reconciled against business metrics; tables, views, constraints, and indexes were checked one by one; an illegal write confirmed constraints were actually enforcing; and finally, file size and hash were recorded. Only a backup that passes this entire process can be called "usable," not just "ran successfully."

A clarification is also needed: this verifies "backup file usability," not a production restore. A real online restore involves additional considerations: downtime windows, connection and permission switching, how the application cuts over, to which point in time to restore — that's material for another article. But conversely, a backup that can't even pass the restore-to-temporary-database checkpoint makes all of the above moot. Running backups on a schedule is only the beginning; periodically pulling them out and verifying them is what truly makes them your safety net.