Your AI App Has a Backup. Can It Actually Recover?

Your AI-assisted app can produce a valid database containing completely wrong business data. A backup job reporting success does not tell you whether you can recover the records your business needs. Before handing a coding assistant another migration, run a restore drill in a disposable environment.

This guide runs a complete small SQLite experiment: create approved sample rows, back them up, simulate an unwanted edit, restore into a separate database and compare the business values. It never opens your production database.

What the drill proves

Fictional scenario: Orbit Workshop stores two draft quote items. A website plan is 15,000 sample cents; a workflow map is 25,000 sample cents. These are invented fixtures, not My Biz Heroes prices or real client quotes. An unwanted SQL edit changes both values to one cent. No AI model generates that edit; the script deliberately simulates the mistake.

The useful distinction is between a database that SQLite can read and a database that contains the right business state. This tiny example compares exact expected rows; your application needs its own invariants, such as approved quote totals, status transitions and record relationships.

Use the database backup interface

Python exposes SQLite backup through Connection.backup(), with a destination connection. The official documentation also explains URI connections, which this example uses to open sources read-only. Python sqlite3 documentation.

SQLite’s backup interface makes a snapshot into a destination database and can replace that destination’s contents. Our wrapper therefore refuses an existing target. This is a guard against accidental reuse in the drill’s private directory, not a race-safe multi-user backup service. SQLite Backup API.

Run it in a fresh folder

You need Python with its standard-library sqlite3 module and permission to create temporary files. No pip packages, API keys, account sign-ins or production connection strings are needed. Save the following complete program as restore_drill.py. Run normal Python, without -O: the drill uses assertions, which optimized mode disables.

"""Disposable fictional database drill. Never opens a production path."""
from pathlib import Path
import json
import sqlite3
import tempfile
from contextlib import closing


def read_only(path):
    return sqlite3.connect(Path(path).resolve().as_uri() + '?mode=ro', uri=True)


def rows(path):
    with closing(read_only(path)) as db:
        return db.execute('SELECT id, label, cents FROM quotes ORDER BY id').fetchall()


def copy_database(source, target):
    source, target = Path(source), Path(target)
    if not source.is_file():
        raise FileNotFoundError(source)
    # This protects accidental reuse in this single-process private directory.
    # It is not a race-safe multi-user publication primitive.
    if target.exists():
        raise FileExistsError(target)
    with closing(read_only(source)) as src:
        with closing(sqlite3.connect(target)) as dst:
            src.backup(dst)


def integrity(path):
    with closing(read_only(path)) as db:
        return db.execute('PRAGMA integrity_check').fetchall()


def drill():
    checks = []
    expected = [(1, 'Website plan', 15000), (2, 'Workflow map', 25000)]
    with tempfile.TemporaryDirectory(prefix='orbit-restore-') as folder:
        base = Path(folder)
        live, backup, restored = [base / name for name in
                                  ['sample.db', 'backup.db', 'restored.db']]
        with closing(sqlite3.connect(live)) as db:
            db.execute('CREATE TABLE quotes(id INTEGER PRIMARY KEY, label TEXT, cents INTEGER)')
            db.executemany('INSERT INTO quotes VALUES(?, ?, ?)', expected)
            db.commit()
        assert rows(live) == expected
        checks.append('seed matches approved fictional rows')
        copy_database(live, backup)
        assert integrity(backup) == [('ok',)] and rows(backup) == expected
        checks.append('backup integrity and business rows match')
        # Simulate an unwanted AI-assisted SQL edit. No AI model is called.
        with closing(sqlite3.connect(live)) as db:
            db.execute('UPDATE quotes SET cents = 1')
            db.commit()
        assert integrity(live) == [('ok',)] and rows(live) != expected
        checks.append('integrity alone misses the wrong business values')
        damaged = rows(live)
        copy_database(backup, restored)
        assert integrity(restored) == [('ok',)] and rows(restored) == expected
        checks.append('isolated restore recovers the approved rows')
        assert rows(live) == damaged
        checks.append('restore does not overwrite the damaged sample')
        try:
            copy_database(backup, restored)
            raise AssertionError('overwrite unexpectedly accepted')
        except FileExistsError:
            checks.append('existing restore target refused')
        missing = base / 'missing.db'
        try:
            copy_database(missing, base / 'unused.db')
            raise AssertionError('missing source unexpectedly accepted')
        except FileNotFoundError:
            assert not missing.exists() and not (base / 'unused.db').exists()
            checks.append('missing source refused without creating files')
        garbage = base / 'garbage.db'
        garbage.write_bytes(b'not a database')
        try:
            integrity(garbage)
            raise AssertionError('garbage unexpectedly accepted')
        except sqlite3.DatabaseError:
            checks.append('non-database file rejected')
        report = {'sqlite_version': sqlite3.sqlite_version,
                  'checks_passed': len(checks), 'checks': checks,
                  'restored_rows': rows(restored),
                  'production_files_opened': False}
    return report


if __name__ == '__main__':
    print(json.dumps(drill(), indent=2))

From the folder containing the file, run:

python -X utf8 restore_drill.py

The program creates and removes its own temporary directory. It opens only authored fixtures inside that directory, prints its evidence and leaves your app untouched. It uses explicit commits and closing() so connections close before temporary files are removed. The full output from this run was:

{
  "sqlite_version": "3.50.4",
  "checks_passed": 8,
  "checks": [
    "seed matches approved fictional rows",
    "backup integrity and business rows match",
    "integrity alone misses the wrong business values",
    "isolated restore recovers the approved rows",
    "restore does not overwrite the damaged sample",
    "existing restore target refused",
    "missing source refused without creating files",
    "non-database file rejected"
  ],
  "restored_rows": [
    [
      1,
      "Website plan",
      15000
    ],
    [
      2,
      "Workflow map",
      25000
    ]
  ],
  "production_files_opened": false
}

Executed environment: Windows, Python 3.14.3 and SQLite 3.50.4. All eight checks passed. The current Python documentation is for the 3.14 branch; that does not mean this run used the newest patch release. Copying this program from the final article and running it in another disposable folder was also checked before publication.

Read the failures as carefully as the success

• Wrong values: the altered sample still returns ok from its integrity check. Exact business-row comparison catches the problem.
• Existing destination: a second restore to the same filename raises FileExistsError. Choose a new isolated target rather than replacing an investigation copy.
• Missing source: the wrapper refuses it before SQLite can create an empty file. The drill checks that neither source nor destination appeared.
• Non-database input: SQLite rejects a garbage file during inspection. A filename ending in .db is not evidence of a valid backup.
• Changed live sample: the restored rows recover, while the intentionally damaged sample remains unchanged. Recovery verification and production replacement are separate decisions.

Turn the experiment into a real recovery requirement

Ask a coding assistant to add a restore test using synthetic fixtures and your app’s business invariants. Supply the approved baseline and expected results yourself. Keep database-copy code and restore tests away from real customer data during development. For changes that need review, pair this with the service catalog change-review guide.

A real plan must answer: which backup is the last known-good state, which recent legitimate changes would be lost, who approves recovery, and how do you keep the damaged database for investigation? Restoring an older snapshot can also discard valid work performed since that snapshot. This script intentionally does not choose a recovery point or replace production files.

Keep actual backups in an access-controlled location appropriate to their data, with retention and a separate failure domain. A second file on the same disk does not protect against losing that disk. Test your application’s restore and startup path in an isolated environment, not just this two-row database comparison.

Limits before you reuse the helper

This is a single-process teaching drill, not an unattended production backup utility. It does not test concurrent writers, WAL-specific behavior, large databases, encrypted databases, network filesystems, disk-full recovery, permissions, retention or app restart. SQLite documents that concurrent writes may restart a backup; this run has no concurrent writers. A failed copy can leave a partial destination, which should remain untrusted until separately validated. The wrapper has no time budget, atomic target reservation, checksums, encryption or recovery log.

The eight checks demonstrate this fixture’s behavior, not a durability guarantee. No real incident, customer savings, AI coding session or host integration is claimed. The featured rescue artwork is a generated conceptual illustration; the interior table reflects actual local results.

Jason Ead publishes AlgoThoughts AI News and connects practical app engineering with My Biz Heroes: websites, automation, workflows, AI tools and training. Explore Jason’s work. A useful recovery check belongs alongside the feature, before the next business-critical edit.