Lesson 6 of 10Diagnostics

16 min · theory and practice · Basics

Restore drills: integrity, data, RPO and RTO

Check that a backup can actually be restored and meets the recovery need.

Separate an existing file from a usable backup and define a verifiable restore plan.

A backup exists to recover data, not to turn a cron job green. Verify creation, independent opening, structure and data, then assess acceptable data loss and recovery time.

Identify the candidate backup

Record the source, creation method, time and backup location. Size and mtime help locate candidates but do not establish completeness. Clocks can disagree, and a plausible filename can contain an empty or old database.

Use a consistent copying mechanism for a live SQLite database, such as the backup API or an appropriate CLI command. Copying only the database file during writes requires understanding its journal and consistency requirements.

Commands
ls -lh /var/backups/shop
stat /var/backups/shop/example.sqlite3

Restore into a separate location

A drill should not overwrite the working database. Create a separate temporary path, restore the selected backup there and test that copy. Replace example.sqlite3 with the actual filename established in the previous step.

Preserving the source makes the experiment repeatable and comparable. Merely starting sqlite3 does not establish that tables exist; the tool can also open a new empty database.

Commands
RESTORE=$(mktemp /tmp/shop-drill.XXXXXX.sqlite3)
sqlite3 "$RESTORE" ".restore '/var/backups/shop/example.sqlite3'"
sqlite3 -readonly "$RESTORE" "PRAGMA integrity_check; SELECT count(*) FROM orders;"
Example output
ok
1248

Verify data meaning as well as structure

integrity_check tests internal database consistency; it does not know which orders the business expects. Check required tables, row counts and a known reference value or date range as well.

If yesterday had 1,200 rows, 1,248 looks plausible but does not establish that the latest required order exists. Choose a criterion tied to the recovery need and save it with the drill result.

State recovery loss and recovery time

RPO describes acceptable data loss in time; RTO describes acceptable service recovery time. Establish requirements, then compare the candidate backup and measured drill against them. An attractive number without a criterion guarantees nothing.

Recovery time includes locating, transferring, validating, reconnecting the application and checking its user operation. Record the drill’s limits: testing SQLite alone is not a complete service recovery.

From symptom to check

ObservationNext checkHow to interpret it
File exists, table missingOpen a separately restored copyA filename and successful CLI startup do not prove content.
integrity_check: okRequired records and a reference markerStructural consistency is not business completeness.
Restorable backup is too oldCompare with the RPO requirementA usable backup can still exceed acceptable data loss.

Common mistakes

  • Running the drill over the live database.
  • Treating mtime and nonzero size as sufficient evidence.
  • Promising a service RTO from one sqlite3 command’s duration.

How to prove recovery

  1. The backup was independently restored to a separate path.
  2. Integrity and data were checked against explicit criteria.
  3. Duration, possible loss and drill boundaries are recorded.

MINI INCIDENT

Integrity without recent orders

The backup opens and integrity_check returns ok, but a reference order from the last two hours is missing. The RPO requirement is one hour. What can and cannot be claimed?

Evidence to investigate
integrity_check → ok
required reference order → missing
RPO target → 1 hour
Explore the diagnosis

You can claim verified structural consistency of that backup. You cannot claim the RPO or required data completeness. Investigate its origin and freshness, the next backup run and the reference-record criterion; one ok is not proof of service recovery.

WORKSHOP

Workshop: explain the evidence and choose an action

Both backups open successfully, but satisfy different requirements.

Sample evidence
backup A: required record present, restore drill 9 min
backup B: required record missing, restore drill 3 min
target RTO=5 min
  1. Which backup is already proven to satisfy all requirements?

    Compare your reasoning

    Neither. A exceeds the stated RTO for the tested drill; B lacks the required record.

  2. What should be investigated to speed up A?

    Compare your reasoning

    Break the nine minutes into locating, transferring, restoring and validating. Optimize the measured bottleneck while retaining data checks.

  3. How should the result be reported without an unsupported promise?

    Compare your reasoning

    Record each backup’s verified data, measured duration and unmet requirement. Schedule the next drill separately.

CHECK YOUR UNDERSTANDING

Why must restoration be a separate, verifiable exercise?

Show explanation

Creating a file does not verify its usefulness, required records or application reconnection procedure. A drill exposes missing steps and provides a measurable outcome.

Documentation

Guest progress is stored separately in this browser.

TAKE IT TO THE TERMINAL

Practice on an incident

Choose a linked case and write three diagnostic checks before changing configuration. After the repair, repeat the original user operation.

DID IT MAKE SENSE?

Was this article helpful?

Rate the article or tell us what needed more explanation. Your feedback helps improve the lessons.

Helpful 0 Not helpful 0

Sign in to rate this article and leave a comment.

Discussion

0

Join the discussion →

No comments yet. Share your first impression or ask a question about this topic.