Accept an AI-written backup job only after checking what its output can restore. A successful copy command and a database that opens without an integrity error do not establish that the required business records are present. Define the recovery point, restore into an isolated destination, and inspect the state the application would actually use.
This is a practical review question for teams using coding assistants to maintain small internal systems. An order tracker, scheduling tool or reporting database can depend on a short backup script that receives less review than the application. Asking the assistant to add a daily copy is easy. Establishing what that copy protects requires a separate acceptance test.
The worked example below uses fictional records and an executed SQLite fixture. It demonstrates one failure of a main-file-only copy, not the reliability of an AI product. No assistant, production backup service or customer system was tested.
Start with the state you expect to recover
Imagine a fictional order tool with two committed records: order O-101 for three units and order O-102 for five units. The backup requirement for this exercise is to recover both records, with their identifiers and values unchanged. There are no concurrent business writes during the copying steps.
An older recovery point might be acceptable under a different policy. That would need an explicit boundary describing which later transactions could be lost. For this example, silently returning to the first order alone fails the stated requirement.
Specify that requirement before generating the implementation. Otherwise, the assistant may produce a script that checks only whether a destination file exists. A reviewer then has no independent basis for deciding whether the restored result is sufficiently recent or complete.
For a real workflow, identify the business operation to resume. “Recover the database” might mean viewing recent orders, continuing a scheduled export or reopening a case with its attached document. Those operations may depend on more than one file or service. Keep the initial acceptance case small, but name what it leaves outside the test.
Why copying a SQLite main file can miss committed work
SQLite’s write-ahead logging mode, usually abbreviated WAL, can keep committed changes in a separate log before they are transferred into the main database file by checkpointing. Its official WAL documentation says that the WAL forms part of the database’s persistent state. Separating the database from that log can lose committed transactions or cause corruption.
For our disposable fixture, we created the table and first order, then performed a checkpoint. We subsequently committed the second order while keeping the connection open. Automatic checkpointing was disabled for the fixture so that this second change remained in the WAL. The live database query returned both orders.
We then compared two approaches: copying only the main database file with Python’s file-copy function, and creating a destination database through Python’s SQLite backup interface. Each destination was opened independently and queried. The source connection remained open through these steps.
The test ran with Python 3.12.14 and SQLite 3.53.1. Both destinations returned ok from PRAGMA integrity_check, but their records differed:
| Destination | Records read from the destination | Integrity check | Meets this fixture’s recovery requirement? |
|---|---|---|---|
| Main-file-only copy | O-101: 3 units | ok | No: O-102 is missing |
| SQLite backup-interface destination | O-101: 3 units; O-102: 5 units | ok | Yes |
Assertions checked the source’s two-record state, a nonempty WAL, both integrity results and the exact ordered records in each destination. The raw copy completed without a copy exception. Its destination was readable and internally valid in this fixture, yet it represented an earlier state than the requirement allowed.
Do not generalize this result into “file copies always fail” or “every WAL omission produces a readable old database.” The documented consequences include corruption. The result depends on the controlled sequence above. Nor is copying the main file and log in two independent operations a demonstrated fix: this test did not establish a consistent multi-file capture procedure.
SQLite provides an Online Backup API for copying a live database into another database. Its documentation describes locking and incremental operation. Use the supported mechanism appropriate to your engine and version, then verify the actual restore path. This small successful backup-interface case does not certify a complete operational backup design.
Restore testing has a long history
Elizabeth Zwicky’s LISA 2003 paper on backup and archive testing used a backup-and-restore pair, then compared resulting file contents and metadata with the original. Its discussion stresses that behavior depends on program versions, operating systems, filesystems and configuration.
That historical research concerned archive tools and deliberately difficult filesystem cases, not today’s AI coding assistants or our SQLite fixture. It supports a testing principle rather than a current failure-rate estimate: inspect the recovered result under the conditions that matter. A command name or an archive’s existence is insufficient evidence.
GitLab’s February 2017 practitioner postmortem provides a separate operational example. Following its 31 January outage, the team found that its expected logical backups were absent. A database-tool version mismatch had caused the backup procedure to terminate, while failure-notification emails were rejected. This is the team’s account of a historical incident, not a description of GitLab’s current service.
Our implication is to check the chain from execution to recovery and notification. A restore test cannot help if no usable artifact is produced, and an alert that never reaches its owner cannot prompt repair.
Give an AI-assisted change a recovery acceptance case
Ask the developer and assistant to keep three outcomes separate: the job ran, the artifact passed its relevant structural checks, and the recovered application state met the stated requirement. Record a failure at any stage without presenting an earlier stage as recovery success.
Our proposed acceptance exercise begins with approved synthetic data. Retain known records with exact expected identifiers, values and relationships. Produce the backup using the intended launcher and environment. Restore into a new, isolated destination and compare the recovered records with that independently prepared expectation.
Then exercise one application operation against the restored state. For the fictional order tool, that could mean opening both orders and preparing a read-only units summary. A row count alone would miss replaced identifiers or altered values. A database check alone would not establish that the application’s required attachments or configuration are available.
Keep recovery exercises from sending real notifications, processing payments or triggering live integrations. Use a destination and credentials appropriate to the rehearsal. Restoring data should not accidentally resume business side effects before an operator has assessed the result.
This complements the guide to preserving reports after failed shell runs. That guide checks what remains after a refresh fails. Here the separate question is whether a retained backup can recreate the required state elsewhere.
Record the boundary of a successful rehearsal
Keep the artifact identifier, capture method, engine and tool versions, recovery point, restore destination, observed records and failed checks together. Measure the actual time needed to make the required application operation usable. Distinguish that observation from a promised recovery time under a larger or less favorable incident.
The SQLite fixture did not test concurrent writers during backup, interrupted transfers, storage failure, encryption-key availability, remote storage, access permissions or a complete application restart. It also did not demonstrate durability after power loss. A production acceptance plan needs the conditions relevant to that system, rather than copying this two-record test unchanged.
For a small team, start with one isolated restore and a named person responsible for reviewing it. A larger organization may need coordinated recovery across databases, object storage and configuration. In either case, repeat the relevant rehearsal after material changes to the backup path or its dependencies.
For the next AI-written backup patch, request one concrete result alongside the script: a restored destination containing the required business state, with the recovery point and remaining limits recorded. That evidence answers a question a successful copy message cannot.