Copying an SQLite file looks like an obvious backup strategy: find the .db file and copy it somewhere safe. That can be acceptable when the database is definitely idle, but it is the wrong abstraction for a database that may be changing while the copy runs.

SQLite provides an Online Backup API specifically for this problem. Python exposes it as sqlite3.Connection.backup(), so an application can copy a live database into another SQLite database while preserving a consistent database snapshot.

The important distinction is that a backup is a database operation, not merely a filesystem operation.

Why copying the file is not the same thing

A database file is structured state whose pages can change as transactions commit. A filesystem copy works at the level of bytes and blocks; it does not inherently coordinate with SQLite’s transaction machinery.

SQLite’s historical safe file-copy procedure required holding an appropriate database lock for the duration of the copy. That prevents writers from changing the database underneath the copy, but it also means writers may have to wait until a potentially large copy finishes.

The Online Backup API takes another approach. It copies the database through SQLite itself and can transfer the source incrementally. The source therefore needs to be read-locked only during individual copy steps instead of necessarily being held for the entire operation.

That makes it useful for applications that need backups without a long maintenance window.

The basic Python backup

Python’s standard sqlite3 module exposes the operation directly:

import sqlite3

with sqlite3.connect('app.db') as source:
    with sqlite3.connect('backups/app.db') as destination:
        source.backup(destination)

The direction matters. backup() is called on the source connection, and the destination connection is passed as its argument.

After a successful backup, the destination is an independent SQLite database. Later writes to app.db do not magically appear in that backup file.

This also works with in-memory databases, which is useful in tests or applications that deliberately keep their working database in memory:

memory_db = sqlite3.connect(':memory:')
disk_db = sqlite3.connect('snapshot.db')

memory_db.backup(disk_db)

The same mechanism can be used in the opposite direction by making the disk connection the source and an in-memory connection the destination.

A live backup is a consistent snapshot

The key guarantee is consistency. SQLite’s backup machinery coordinates with the source database so that the completed destination represents a valid snapshot rather than an arbitrary mixture of database pages copied at unrelated transaction states.

Other clients may continue to access a live source while the backup runs. SQLite documents the source as being read-locked while a backup step is actually reading it, rather than continuously for the entire incremental operation.

If another connection modifies the source between backup steps, SQLite can detect that change and adjust the backup process. In some cases the backup has to restart internally. The final result remains a consistent database when the operation completes.

This leads to an operational caveat: a database under extremely heavy continuous write load can make an incremental backup take longer than its nominal size suggests. Backup duration is therefore something worth observing in production rather than assuming it scales only with file size.

Copy in batches when responsiveness matters

Connection.backup() accepts a pages argument. A positive value limits how many database pages are copied per backup iteration:

import sqlite3

source = sqlite3.connect('app.db')
destination = sqlite3.connect('backups/app.db')

try:
    source.backup(destination, pages=128, sleep=0.05)
finally:
    destination.close()
    source.close()

If pages is zero or negative, Python requests that all remaining pages be copied in one step. A positive page count gives SQLite opportunities to release the source read lock between iterations.

The sleep argument controls how long Python waits between successive attempts when pages remain to be copied. It should not be interpreted as a generic bandwidth throttle. The right values depend on database size, storage speed, write traffic, and how sensitive the application is to lock contention.

Do not optimize these parameters by intuition alone. Measure backup duration and application latency under realistic concurrent writes.

Report progress without guessing from file size

Python can call a progress function after backup iterations:

import sqlite3


def report(status: int, remaining: int, total: int) -> None:
    copied = total - remaining
    print(f'{copied}/{total} pages copied; status={status}')


with sqlite3.connect('app.db') as source:
    with sqlite3.connect('backups/app.db') as destination:
        source.backup(
            destination,
            pages=128,
            progress=report,
            sleep=0.05,
        )

The callback receives the SQLite status from the latest iteration, the number of pages remaining, and the total source page count observed for that iteration.

Treat those counts as progress telemetry, not an immutable prediction. The source can change while an online backup is underway, and SQLite’s low-level page-count values are updated by backup steps. A percentage can therefore be useful for operators without being a contractual estimate of the exact finishing time.

Progress callbacks should also remain cheap. Performing slow network requests or substantial database work from the callback can turn observability into part of the backup’s critical path.

Give the destination exclusive ownership during the copy

A useful rule is to create a dedicated destination connection for each backup and not use it for unrelated work until the backup finishes.

At the SQLite API level, the destination is held in a write transaction during the backup operation. SQLite’s documentation specifically warns against concurrent API use of that destination connection while a backup is in progress.

This pattern keeps ownership obvious:

from pathlib import Path
import sqlite3


def create_backup(source_path: Path, destination_path: Path) -> None:
    destination_path.parent.mkdir(parents=True, exist_ok=True)

    source = sqlite3.connect(source_path)
    destination = sqlite3.connect(destination_path)

    try:
        source.backup(destination, pages=256, sleep=0.05)
    finally:
        destination.close()
        source.close()

If the application already has a long-lived source connection, it may use that connection where the surrounding threading rules permit. The destination should still have a clear single purpose and lifetime.

Back up to a temporary path before publishing

A successful SQLite backup does not automatically solve the rest of the backup lifecycle. For example, another process should not discover a destination filename halfway through creation and assume it is the completed artifact you intend to retain.

One practical pattern is to create the database at a temporary path, close it, verify it, and then publish it under its final name using an atomic rename when the filesystem supports that operation:

from pathlib import Path
import os
import sqlite3


def publish_backup(source_path: Path, final_path: Path) -> None:
    temporary_path = final_path.with_suffix(final_path.suffix + '.tmp')

    if temporary_path.exists():
        temporary_path.unlink()

    source = sqlite3.connect(source_path)
    destination = sqlite3.connect(temporary_path)

    try:
        source.backup(destination, pages=256, sleep=0.05)
    finally:
        destination.close()
        source.close()

    check = sqlite3.connect(temporary_path)
    try:
        result = check.execute('PRAGMA integrity_check').fetchone()
        if result != ('ok',):
            raise RuntimeError(f'backup integrity check failed: {result!r}')
    finally:
        check.close()

    os.replace(temporary_path, final_path)

The rename is not what makes the SQLite contents consistent; the backup API does that. The rename gives the surrounding file-management system a cleaner boundary between an artifact being prepared and one declared ready.

Real backup systems should also clean up stale temporary files and avoid having two backup jobs target the same temporary or final path concurrently.

Integrity checks are useful, but restoration tests are stronger

PRAGMA integrity_check can detect many forms of structural database corruption. Running it against a newly created backup is a useful verification step, especially before uploading or rotating the artifact.

But a structurally valid database is not necessarily a usable application backup.

A restoration test can catch a different class of mistakes:

  • the wrong source database was backed up;
  • required attached databases were omitted;
  • application migrations cannot read the restored schema;
  • encryption keys or external files were not retained;
  • the retention pipeline uploaded an empty or stale artifact;
  • recovery instructions no longer match the application.

The highest-confidence test is therefore periodic recovery into an isolated environment followed by application-level checks.

A backup that has never been restored is an untested recovery hypothesis.

Attached databases need explicit thought

A SQLite connection can have databases attached under names other than main. Python’s backup() method has a name parameter that selects the source database to copy; main is the default.

That means one call should not be assumed to capture an application’s entire universe merely because all the databases are visible through one connection. If application correctness depends on multiple database files, define what a consistent recovery point means across those files and design the backup procedure accordingly.

The same principle applies to state outside SQLite. Uploaded files, search indexes, message queues, encryption material, and object storage may all participate in the application’s recovery story.

WAL mode does not require manually copying sidecar files

Applications using write-ahead logging may have a -wal file next to the main database. This is another reason to prefer SQLite-aware backup mechanisms over ad hoc file copying.

When you use the Online Backup API, SQLite reads the logical database through its database engine. Your backup code should not try to improve that operation by separately copying the source -wal or -shm files into the destination set.

If you choose a raw filesystem-level backup strategy instead, WAL handling becomes part of the correctness problem and must be designed according to SQLite’s documented rules rather than guessed from filenames.

Online backup and VACUUM INTO solve overlapping problems

SQLite also supports VACUUM INTO, which creates a new database file containing a compacted copy of a live database. It can be attractive when producing a minimized copy is part of the goal.

The Online Backup API is a better fit when you want programmatic incremental copying, progress information, or copying to and from in-memory databases. VACUUM INTO and online backup are therefore alternatives with different operational properties, not two steps that every backup must perform.

Choose the mechanism based on the recovery artifact you need and the load characteristics of the production database.

A backup job needs failure semantics

A production backup routine should make failure visible. Do not catch sqlite3.Error, print a message, and then mark the scheduled job successful.

At minimum, distinguish these states:

  1. the backup started;
  2. SQLite completed the copy;
  3. verification passed;
  4. the artifact was published or uploaded;
  5. retention metadata was recorded.

If step 2 succeeds but an upload fails, the local snapshot may still be useful, but the off-site backup objective has not been met. Monitoring should reflect the actual recovery requirement rather than merely whether Connection.backup() returned.

Also avoid deleting the previous known-good backup before its replacement is verified. Retention should promote a new artifact first and remove expired artifacts afterward.

Test concurrency, not just the happy path

A backup feature can appear correct in a unit test that creates ten rows and immediately copies an idle database. The important tests exercise boundaries closer to production:

  • write transactions committing while a backup is running;
  • a database large enough to require many backup iterations;
  • destination lock or I/O failures;
  • interruption before the temporary artifact is published;
  • verification failure;
  • restoring the resulting database and running real queries;
  • repeated scheduled backups with retention enabled.

For concurrency tests, assert invariants in the restored snapshot instead of assuming it must contain whichever write happened nearest to a particular wall-clock instant. The contract you care about is a consistent database state, not a race-dependent row count.

The main idea

A live SQLite database should be backed up through a mechanism that understands SQLite’s transactional state. Python’s sqlite3.Connection.backup() provides direct access to SQLite’s Online Backup API and can create a consistent destination while other clients continue using the source.

Use incremental page copying when responsiveness matters, keep the destination connection dedicated to the backup, publish only completed artifacts, and verify more than file existence. Most importantly, test restoration.

The purpose of a backup is not to create another .db file. It is to make recovery predictable when the original database is no longer available.

SQLite Python Database Backup