Python SQLite backup project: copy a committed snapshot to another connection

Operation contract

The fixture commits one receipt in a source in-memory database, backs it up to a second in-memory connection and checks the destination row. It then inserts another source row. The destination still has the earlier snapshot until another backup is run. Both connections are owned and closed by the program.

Failure and ownership boundary

A backup is not a verified disaster-recovery plan. A production copy needs destination storage, integrity checks, retention, restore drills and coordination with external side effects. Python sqlite3 transactions: bind values and roll back failed batches, Python SQLite checkpoint project: commit data and cursor together and Python file project: stage a report before replacing the visible file describe other persistence boundaries.

Working program

import sqlite3

source = sqlite3.connect(":memory:")
destination = sqlite3.connect(":memory:")
source.execute("CREATE TABLE receipt(id TEXT PRIMARY KEY)")
with source:
    source.execute("INSERT INTO receipt VALUES ('R41')")
source.backup(destination)
with source:
    source.execute("INSERT INTO receipt VALUES ('R42')")
print("source:", source.execute("SELECT COUNT(*) FROM receipt").fetchone()[0])
print("copy:", destination.execute("SELECT COUNT(*) FROM receipt").fetchone()[0])
source.close()
destination.close()

Output

source: 2
copy: 1

Costs and limits

Backup copies pages and requires storage proportional to the destination database. This two-row, in-memory demonstration does not measure backup duration or test a power-loss recovery path.

Common Mistakes

• A one-time backup does not track later source commits.
• Validate that a backup can be restored, not only that the API returned.

Connected lessons

Python sqlite3 transactions: bind values and roll back failed batches, Python SQLite checkpoint project: commit data and cursor together, Python file project: stage a report before replacing the visible file.