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.
ls -lh /var/backups/shop
stat /var/backups/shop/example.sqlite3Restore 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.
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;"ok
1248Verify 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
| Observation | Next check | How to interpret it |
|---|---|---|
| File exists, table missing | Open a separately restored copy | A filename and successful CLI startup do not prove content. |
| integrity_check: ok | Required records and a reference marker | Structural consistency is not business completeness. |
| Restorable backup is too old | Compare with the RPO requirement | A 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
- The backup was independently restored to a separate path.
- Integrity and data were checked against explicit criteria.
- 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?
integrity_check → ok
required reference order → missing
RPO target → 1 hourExplore 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.
backup A: required record present, restore drill 9 min
backup B: required record missing, restore drill 3 min
target RTO=5 minWhich 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.
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.
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.
No comments yet. Share your first impression or ask a question about this topic.