Backing up a live SQLite database: use the backup API, not a file copy

📁 Many small databases

Our system keeps its state in separate SQLite files, one per module: traces, a ledger, caches, queues. On 3 November 2026, 21:53 UTC the data folder held 128 database files totalling 12.0 GB. That is a normal and pleasant way to build small services, and it means that backup is a job you must get right many times over.

⚠️ Why a copy is not a backup

A SQLite database is one file plus, in write-ahead mode, a second log file. A writer may be in the middle of a transaction while you copy. Copy the main file and then the log at different moments and you hold two halves of different times. The result may open and may even look fine, and contain missing or inconsistent rows.

One reviewer objection in October 2026 made this concrete. A file that "has not changed in a long time" is not safe to copy: a writer can begin a transaction while the copy is running. Age of the file is not a lock.

✅ The safe way

SQLite ships an online backup interface. It copies the database page by page into a destination file and keeps the result consistent even if the source is written to during the copy. Our backup script uses it with a small page count per step and a short sleep between steps, so a big database does not block writers for long. We run the copies one after another, not in parallel, to keep the disk and the load calm.

Two accounting rules came out of the 14 September 2026 incident, when the disk reached ninety-seven percent because backups had no rotation. First, count the space for the file plus its log, in the same pass that makes the copy. Second, the guard's own comment names missing rotation as the cause, so rotate old copies in the same job that makes new ones, and the job cannot grow without bound.

🛠️ Practice: build the backup job

1. List your database files and mark which are live. Include the write-ahead log files in the size.
2. Write a backup script that opens each source, opens a destination file and runs the backup interface in steps of a few hundred pages with a short pause.
3. Run the databases one by one. Log the size of each copy and the time it took.
4. After each copy open the destination and run an integrity check. Record the result.
5. Keep a fixed number of recent copies and delete older ones in the same run.
6. Wrap the job with the load-shedding wrapper so that it does not start on a busy host.
7. Once a month restore one copy into a scratch folder and read a few rows.

✅ Check yourself

• Start a writer that inserts rows in a loop. Back up while it runs. The copy passes the integrity check.
• Count the disk space your backups use today and next month. It does not grow without limit.
• Restore the latest copy to a scratch folder. The application opens it and reads recent data.
• The job log says how long each database took and whether the check passed.

🚧 When this does NOT work

• Very large databases. A step-wise copy of hundreds of gigabytes takes hours. Plan the window and the disk.
• Heavy write load. A busy writer can make the backup restart pages many times. Back up at a quiet hour.
• Same disk. A backup on the disk you are protecting does not survive a disk loss. Copy it off the machine.
• No restore test. An unverified backup is a hope. Test restores on a schedule.
• Other stores. Files outside SQLite, such as uploads, need their own backup.

📊 Evidence

➡️ Next

Plan restoring in Recovery after a failed, stuck or uncertain run, and keep the job from starving production with load shedding for scheduled jobs.

Sources

• Recovery after a failed, stuck or uncertain run: restoring.
• Permissions and data flow: what is stored where.
• An AI incident playbook for a small team: incident steps.
• Agent task receipts: what they prove: records of runs.