Python SQLite Backup With a Verified Restore Copy
Back up a live SQLite database, verify its contents, and practice restoring the result with a small reproducible example.
Checked against the official docs on Nov 14, 2026.

Use Python's sqlite3.Connection.backup() to copy a database that an application is still using. Open a separate destination, finish the backup, then check that the resulting database can be queried before treating it as a restore point.
The in-memory example and file-backup script were tested on November 14, 2026 with Python 3.12.3 on Linux. The file test kept a WAL-mode source connection open and verified the committed record in the restored copy.
Start with a backup you can inspect
The example below creates a small database entirely in memory, backs it up to another connection, and changes the source afterward. It gives you two concrete things to inspect. The destination contains the committed records copied during backup, and a later source change stays in the source. Both connections are closed explicitly through context managers.
import sqlite3
from contextlib import closing
with closing(sqlite3.connect(":memory:")) as source:
source.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT)")
source.executemany(
"INSERT INTO notes(title) VALUES (?)",
[("first draft",), ("reviewed guide",)],
)
source.commit()
with closing(sqlite3.connect(":memory:")) as backup:
source.backup(backup)
source.execute("INSERT INTO notes(title) VALUES (?)", ("later edit",))
source.commit()
titles = backup.execute("SELECT title FROM notes ORDER BY id").fetchall()
print("backup titles:", titles)
print("source rows:", source.execute("SELECT count(*) FROM notes").fetchone()[0])
print("backup rows:", backup.execute("SELECT count(*) FROM notes").fetchone()[0])
print("integrity:", backup.execute("PRAGMA integrity_check").fetchone()[0])The useful assertion is the difference in row counts. If both counts changed together, the exercise would have failed to create an independent copy. Run this before adapting the file-based version so you know what a completed backup should mean for your application.
Python exposes page-sized copying, a progress callback, and a pause between retry attempts through the backup method. A backup can run while other connections access the source. The Python API reference describes those parameters and the accepted destination connection. Python SQLite backup reference
Back up an existing file without creating a missing source
Opening a misspelled path with the default connection settings can create a new, empty database. A backup job should treat a missing source as a failure. This version resolves an existing path and opens it read-only, then writes a uniquely named candidate file in the destination directory. Run it as python backup.py app.db backups after saving the following code as backup.py.
import sqlite3
import sys
import uuid
from contextlib import closing
from datetime import datetime, timezone
from pathlib import Path
source_path = Path(sys.argv[1]).resolve(strict=True)
destination_dir = Path(sys.argv[2]).resolve()
destination_dir.mkdir(parents=True, exist_ok=True)
stamp = datetime.now(timezone.utc).strftime("%Y%m%dT%H%M%S")
candidate = destination_dir / f"backup-{stamp}-{uuid.uuid4().hex}.partial"
finished = candidate.with_suffix(".sqlite3")
try:
with closing(sqlite3.connect(source_path.as_uri() + "?mode=ro", uri=True)) as source:
with closing(sqlite3.connect(candidate)) as destination:
source.backup(destination, pages=128, sleep=0.1)
integrity = destination.execute("PRAGMA integrity_check").fetchall()
if integrity != [("ok",)]:
raise RuntimeError(f"integrity check failed: {integrity}")
foreign_keys = destination.execute("PRAGMA foreign_key_check").fetchall()
if foreign_keys:
raise RuntimeError(f"foreign key violations: {foreign_keys}")
candidate.replace(finished)
except BaseException:
candidate.unlink(missing_ok=True)
raise
print(finished)The final filename is the success signal for this job. An exception keeps the candidate out of the completed-backup set. The timestamp makes the directory readable to an operator, while the random suffix allows two invocations within the same second to create different candidates. Choose a directory writable only by the account responsible for backups, particularly when the database contains private records.
This script assumes one main database and a local destination filesystem. If your application uses attached databases, inventory them explicitly and define what consistency across those files means. A backup of one connection's main database is only one part of that inventory. Include related uploads, configuration, and encryption material in your own recovery plan where the application requires them.
For an introduction to connections, transactions, and parameterized queries, see the existing Python SQLite examples.
Account for writes and the WAL file
An active SQLite database may have committed changes in its write-ahead log. Copying the main database file by itself can therefore lose changes that the application already considers saved. The backup API reads through SQLite's database machinery. SQLite's WAL documentation explains why the database and its WAL belong together when handling a live database at the file level. SQLite write-ahead logging
Build a small rehearsal that resembles your workload. Keep an application connection open, insert a known record, commit it, and run the backup process from a second connection. Open the resulting file and look up that record by its primary key. Then add another record and verify which backup contains it. Record the time each operation finished so you can explain the observed result without guessing from file modification times.
Ongoing writes can cause backup work to restart. A busy database can take longer to copy than a quiet database of the same size. The page count in the sample controls work per step. It should be selected using measurements from your application, especially if reader latency is important. SQLite documents restart behavior and the possibility that sufficiently frequent writes prevent completion. SQLite online backup behavior
The sample leaves the total wall-clock deadline to its supervisor. If a scheduled job needs one, supervise it as a separate process and retain the previous successful backup until the new one is complete. Monitor failures and unusually old successful backups. A scheduler that merely starts the script cannot tell you whether a usable restore point was produced.
Check structure and application data separately
An integrity check examines database structure. Foreign-key checking examines relationships declared in the schema. Your application's expectations need additional queries. For a content site, those might include the number of published articles, the latest publication timestamp, and the presence of the settings row that identifies the site. For an inventory service, inspect a known item and a recent stock movement.
| Check | What to record | Follow-up when it fails |
|---|---|---|
| Integrity check | All returned rows | Quarantine the candidate and inspect the source |
| Foreign-key check | Each violating relationship | Determine whether the violation already exists in production |
| Important table count | Count plus backup filename | Compare with expected application activity |
| Known record lookup | Stable identifier and selected fields | Confirm the source path and transaction timing |
| Test application startup | Version and startup result | Investigate schema compatibility and missing companion files |
An empty foreign-key result is the expected result when declared relationships are consistent. Integrity checking has its own scope, and foreign-key validation is a separate pragma. Keep both outputs when investigating an unexpected backup failure. SQLite validation pragmas
Avoid logging complete user records just to prove that a backup works. A row count, an internal test identifier, and the existence of required columns often provide enough evidence for an operator. Store any detailed inspection output with the same access restrictions as the backup itself.
Rehearse a restore into a separate directory
Create a disposable restore directory and copy one completed backup there. Start a test instance of the application against that copy using the application version intended for recovery. Exercise one read and one write, then restart it and check that the write persisted. This catches assumptions that a SQL-level check cannot exercise, such as a startup migration or a missing file that the application expects beside the database.
Keep the running production database out of this rehearsal. Replacing an open database file can leave processes referring to different file states. A real restore requires a controlled application stop, selection of the intended backup, handling of journal sidecars, and a restart procedure appropriate to the service. Document those steps while rehearsing so recovery does not depend on reconstructing them during an incident.
Finally, retain more than the newest file. Decide how far back you need to recover from an accidental edit that goes unnoticed, then apply retention only to completed backups. Test that the backup account can read older files and that a second storage location is actually reachable. A useful backup routine produces a named artifact, a successful validation result, and a demonstrated path back to a working application.
Quick answers
Can I back up SQLite while the application is running?
Yes. Use the online backup API through Connection.backup and verify the completed destination. Heavy concurrent writes can delay completion.
Why does the script open the source read-only?
A missing or mistyped source should fail. A normal writable connection could create an empty database at the wrong path.
Is an integrity check enough to verify a backup?
Add foreign-key checks, application-specific queries, and a restore rehearsal with the intended application version.
Official sources
Next guide