How to Test Your SQLite Backups

A restore-first checklist for verifying SQLite backups before an outage forces the issue.

A backup test is not a file listing

The easiest backup test is checking that an object exists in a bucket. It is also the least useful. A file listing does not prove the object is complete, readable by the current credentials, compatible with your restore command, or recent enough to matter.

The useful test is closer to what you would do during an incident: start from the backup, restore to a clean path, open the database, run checks, and write down the result. If you cannot do that calmly on a normal Tuesday, you will not enjoy inventing it during an outage.

The manual drill

Pick a project and restore it to a temporary location that your application is not using. Never test by overwriting the production database. Open the restored file read-only, run integrity_check, list tables, and count the tables that would make an outage painful.

Record the timestamp, restored size, command used, and how long it took. The point is not ceremony. The point is creating an incident note before the incident exists.

borela-agent restore -project app -output /tmp/restored.db
sqlite3 /tmp/restored.db 'PRAGMA integrity_check;'
sqlite3 /tmp/restored.db '.tables'
sqlite3 /tmp/restored.db 'SELECT count(*) FROM users;'

What a passing result looks like

A passing drill should be boring. The restore command exits successfully. SQLite opens the file. PRAGMA integrity_check returns ok. The important tables exist. Row counts are plausible. The restored file is large enough to make sense. You know which timestamp you restored.

Do not chase perfect validation on day one. Counting every row in every table is useful for automation, but the first win is knowing that the restore path is real and repeatable.

Test the things that usually break

Credentials drift. Buckets get renamed. Environment variables disappear during a deploy. Retention policies delete more than intended. The app moves from /var/www/app to /home/deploy/app and the backup service keeps watching the old path.

That is why a restore drill should exercise storage credentials and the actual restore command, not only the local database file. You want to catch operational drift while the app is healthy.

When to alert

Alert when the latest backup is stale, when no snapshots exist, when restore fails, when integrity_check fails, and when credentials can no longer read or write the replica. These alerts are worth sending because every one maps to a specific recovery risk.

Successful drills are different. Send the first success so the operator knows the system is working. After that, record success in the dashboard and reserve email for failures unless the customer explicitly wants a weekly report.

A simple schedule

For a small production app, run a restore drill weekly. If the database changes constantly or the business is more sensitive to data loss, run it daily. For non-critical side projects, monthly is better than never.

The schedule matters less than consistency. A restore drill that runs every Friday and sends a failure email beats a perfect playbook that nobody opens.