SQLite Backups: Why Copying the .db File Can Miss Data

You copy app.db into a backup folder. It opens without an error, and SQLite's integrity check returns ok. Yet a record that the application had already saved is missing.

That is possible in write-ahead logging (WAL) mode, where committed changes can still live in a separate file beside the main database. A filesystem copy of app.db alone can leave those changes behind. SQLite's WAL documentation explicitly warns about separating the two.

For a running database, use a SQLite-aware backup operation, such as Python's Connection.backup(), then reopen the result and check the data you expect to recover. This guide demonstrates why both steps matter and provides a script you can adapt for an ordinary local SQLite database you manage.

The examples were run with Python 3.12.14 and SQLite 3.53.1 on Linux x86_64. They need Python 3.12 or newer and its standard-library sqlite3 module. This example assumes a database that the standard sqlite3 module can open and validate. For encrypted or extension-dependent databases and third-party application profiles, use compatible tools and the application's documented backup procedure.

One database can involve more than one file

In WAL mode, a commit does not require SQLite to rewrite the main database immediately. Changes go into the WAL; a later checkpoint transfers them into the main file. SQLite reads the appropriate state through its database connection. A file-copy utility does not perform that database-level work. See SQLite's explanation of WAL commits and checkpoints .

For a database named app.db, the neighbouring files have different jobs:

These roles are documented in SQLite's WAL file-format reference . They are an explanation of the files, not a cleanup instruction. Leave a running application's sidecars under SQLite's control.

Copying the files one after another while the application changes them does not establish a consistent snapshot. SQLite also warns that ordinary copying during a transaction can mix old and new content. This concern is broader than WAL: a live rollback-journal database also needs a coordinated backup.

Reproduce a backup that passes its integrity check but misses a row

This experiment creates its own temporary directory and removes it afterwards. It never opens an existing application database. Save the code as wal_backup_demo.py and run it normally, without Python's -O option, so its assertions remain enabled.

The experiment first checkpoints an empty table into the main file. It then commits one row while keeping the connection open. Automatic checkpoints are disabled only inside this disposable experiment to keep that row in the WAL long enough to compare two copies.

"""A disposable demonstration; never point it at an existing database."""

from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory
import shutil
import sqlite3


def inspect(connection):
    check = connection.execute("PRAGMA integrity_check").fetchall()
    rows = connection.execute("SELECT id, body FROM notes ORDER BY id").fetchall()
    return check, rows


with TemporaryDirectory(prefix="sqlite-backup-demo-") as directory:
    folder = Path(directory)
    database = folder / "live.db"
    with closing(sqlite3.connect(database, autocommit=True)) as live:
        assert live.execute("PRAGMA journal_mode=WAL").fetchone()[0] == "wal"
        # Disable automatic checkpoints only to make this experiment repeatable.
        live.execute("PRAGMA wal_autocheckpoint=0")
        live.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT)")
        assert live.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone()[0] == 0

        # autocommit=True makes this statement its own committed transaction.
        live.execute("INSERT INTO notes VALUES (1, 'Committed before backup')")
        assert Path(str(database) + "-wal").stat().st_size > 0
        print("Live database rows:", len(inspect(live)[1]))

        # Intentionally wrong: a raw main-file copy, with the WAL left behind.
        shutil.copy2(database, folder / "raw-copy.db")
        with closing(sqlite3.connect(folder / "raw-copy.db", autocommit=True)) as raw:
            check, rows = inspect(raw)
            print("Raw copy integrity:", check)
            print("Raw copy rows:", len(rows))
            assert check == [("ok",)] and rows == []

        with closing(sqlite3.connect(folder / "backup.db", autocommit=True)) as backup:
            live.backup(backup)
            check, rows = inspect(backup)
            print("SQLite backup integrity:", check)
            print("SQLite backup rows:", len(rows))
            assert check == [("ok",)]
            assert rows == [(1, "Committed before backup")]

print("All checks passed; temporary files removed.")

Run it with your Python 3.12-or-newer interpreter:

python wal_backup_demo.py

If your installation uses python3, use that command consistently instead. On Windows, if the terminal and editor select different runtimes, check the Python interpreter that actually runs your code before changing the example. No package installation is required.

Our run produced:

Live database rows: 1
Raw copy integrity: [('ok',)]
Raw copy rows: 0
SQLite backup integrity: [('ok',)]
SQLite backup rows: 1
All checks passed; temporary files removed.

The raw copy contains a valid empty table. Its integrity check has no way to infer that this particular backup was supposed to contain the later note. The SQLite backup contains the row because it copies through the database engine's view of the data.

This demonstrates a missing committed record under controlled conditions. It is not a simulation of a power cut or a measurement of backup performance. The source row remains intact throughout; the loss is in the incomplete backup.

Back up a live database through SQLite

Python exposes SQLite's backup mechanism as `Connection.backup()` . It can copy a database while other clients use it, and it supports copying in page-sized batches.

Allowing concurrent access does not mean every operation is free of waiting. SQLite acquires locks while doing the work, and changes through another connection can restart an incremental backup. A successful result is consistent, but you should not promise that it represents exactly the instant the function was called. The Backup API reference describes these rules.

The following script uses a separate connection to an existing source, writes into a new directory and sets that new copy to rollback-journal mode before closing it. The result is a standalone snapshot file, which it reopens for validation. It deliberately avoids replacing a previous backup: the underlying Backup API can replace a destination database's contents .

Save it as backup_sqlite.py:

"""Back up one existing, ordinary SQLite database. Requires Python 3.12+."""

import argparse
from contextlib import closing
from pathlib import Path
import shutil
import sqlite3
import sys
import tempfile
import time

parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument("source", type=Path)
parser.add_argument("backup_parent", type=Path)
args = parser.parse_args()
source = args.source.expanduser().resolve(strict=True)
parent = args.backup_parent.expanduser().resolve(strict=True)
if not source.is_file() or not parent.is_dir():
    parser.error("Source must be a file; backup_parent must be an existing directory.")

folder = Path(tempfile.mkdtemp(prefix="sqlite-backup-", dir=parent))
destination = folder / "snapshot.sqlite"
deadline = time.monotonic() + 60


def progress(status, remaining, total):
    # Checked between backup steps; this is not a hard wall-clock deadline.
    if time.monotonic() > deadline:
        raise TimeoutError("Backup exceeded its 60-second progress deadline.")


try:
    with closing(sqlite3.connect(
        source.as_uri() + "?mode=ro", uri=True, autocommit=True, timeout=1.0
    )) as original:
        with closing(sqlite3.connect(destination, autocommit=True)) as snapshot:
            original.backup(snapshot, pages=256, progress=progress, sleep=0.1)
            # Make only the new backup a standalone file without WAL sidecars.
            mode = snapshot.execute("PRAGMA journal_mode=DELETE").fetchone()
            if mode != ("delete",):
                raise RuntimeError(f"Could not finalise backup journal mode: {mode!r}")

    # Close the writer and validate the saved file through a new connection.
    with closing(sqlite3.connect(
        destination.as_uri() + "?mode=ro", uri=True, autocommit=True
    )) as restored:
        result = restored.execute("PRAGMA integrity_check").fetchall()
        if result != [("ok",)]:
            raise RuntimeError(f"Backup integrity check failed: {result!r}")
except BaseException:
    try:
        shutil.rmtree(folder)  # Only the new directory created by this run.
    except OSError:
        print(f"Incomplete backup remains in: {folder}", file=sys.stderr)
    raise

print(f"Backup: {destination}")
print("Reopened backup integrity: ok")
print(f"Python: {sys.version.split()[0]}; SQLite: {sqlite3.sqlite_version}")

Create an empty backups folder, choose the existing database you intend to copy, then run:

python backup_sqlite.py ./app.db ./backups

Here, app.db is an example source path. Replace it with your own database's path and quote paths containing spaces. The parent backup folder must already exist. Each successful run prints the path to a new snapshot.sqlite; the script keeps that directory after it exits.

Several details are worth keeping when you adapt the script:

• A typo must fail. `Path.resolve(strict=True)` rejects a missing source. The source connection also uses SQLite's read-only URI mode , which cannot create a database at a mistaken path.
• Every run gets its own destination. `mkdtemp()` creates a new directory under the chosen parent. A failure removes only that run's directory; an earlier backup remains separate.
• Connections actually close. contextlib.closing closes them before the saved file is reopened. Python's ordinary connection context manager handles transactions but does not close the connection .

The 60-second progress deadline is an example policy, checked between backup steps. It is not a hard wall-clock limit and does not cover the later integrity check. The connection's timeout=1.0 controls lock waiting, not the total duration. For a large or busy database, choose an appropriate operational deadline and monitor whether the job finishes.

Read-only WAL access also depends on access to the database's sidecars or permission for SQLite to create them; mode=ro does not bypass filesystem permissions. SQLite documents the conditions for reading a WAL database . If this fails for another application's private data, use that application's export or backup feature instead of weakening its permissions.

We checked the helper against a live WAL database, filenames containing URI-sensitive characters, two successive runs, a missing source and an invalid database file. A separate-process writer also remained active during a successful backup; the restored copy preserved the tested transaction invariant. These checks establish the example's behaviour in the stated environment, not recovery from every storage failure.

Choose a method that fits the application

The helper backs up one database, main. Python's backup interface lets callers choose a database name, but it does not automatically gather every attached database or external file used by an application.

Suppose a notes application stores note text in SQLite and attachments in a separate directory. A sound SQLite snapshot could still be unusable if the matching attachments are missing. Decide how the application's database, files and configuration will be captured together. Follow its documented maintenance or export procedure when those components must agree.

Keep completed snapshots separate from the live database, retain earlier versions and copy verified snapshots to storage that survives loss of the original machine. Treat those copies as containing the same sensitive information as the source. These are recovery-policy decisions; the script does not provide encryption, retention management or off-device storage.

When VACUUM INTO is a useful alternative

If you want a compact database copy through SQL, SQLite also supports:

VACUUM INTO 'snapshot-2026-10-28.sqlite';

Run this through a connection to the intended source, outside an active transaction, with a new output filename. The command produces a consistent, compact copy without rewriting the source. The target must be absent or empty. Compared with the Backup API, it uses more CPU and cannot be broken into the same incremental steps. SQLite's VACUUM documentation describes the trade-offs.

This is a documented alternative, not a command used by the Python helper. Let it finish successfully before treating its output as a backup; interrupted output can be incomplete. Also check application assumptions about implicit row identifiers, because VACUUM can change the copy's implicit rowids in tables without an explicit INTEGER PRIMARY KEY.

When an ordinary file copy can work

A plain copy can be valid when the database is quiescent throughout the copy and all required database and journal state is preserved. SQLite's backup and corruption guidance sets out that condition.

An app window disappearing is not proof that all background connections have stopped. A normal final close usually checkpoints the WAL and removes the sidecars, but abnormal exits, read-only final connections and persistent-WAL settings are exceptions. The file lifecycle documentation explains why a remaining WAL must not be dismissed as rubbish.

Likewise, “checkpoint, then copy” leaves a gap in which another writer can commit. Checkpoints can also be incomplete or report busy conditions, as the checkpoint API documents. Unless you have coordinated a stable offline state, use a SQLite-aware backup operation.

Test the data you would restore

A successful copy operation and an ok result are useful evidence. The opening experiment shows why they are insufficient as the final acceptance test.

SQLite's `integrity_check` checks database structure and several constraints. Foreign-key violations require the separate `foreign_key_check` . Neither check knows which application record you intended to preserve.

For a recovery rehearsal:

1. Restore into a separate directory. Keep the verified snapshot and the live database intact. Use an isolated application instance with the restored copy.
2. Run the database checks. Expect a single ok row from PRAGMA integrity_check. If the schema uses foreign keys, investigate any rows returned by PRAGMA foreign_key_check.
3. Check known content. Look for a record committed before the backup began whose expected values stayed unchanged throughout the backup. Use a stable identifier or an application-defined recovery marker with that guarantee. A row count alone cannot detect every lost update or wrong value.
4. Try the application workflow. Open a known note, load its attachment or run another representative read. Keep the rehearsal isolated from production integrations.

Our notes example checks the exact tuple (1, "Committed before backup"). An application with changing data needs its own acceptance rule. Comparing a snapshot with the live database much later will naturally find differences, so record what the backup was expected to contain at the time.

Do not restore a main database file over an actively used database or combine it with unrelated old sidecars. SQLite's warning about mismatched databases and journals explains the risk. Practise the application's supported restore procedure before relying on it in an incident.

Keep the SQLite runtime maintained

Check the library used by the actual application and backup process. A Python version number does not identify its SQLite library version; the helper prints both.

This matters beyond backup design. SQLite fixed its separate WAL-reset corruption bug in 3.51.3, released on 13 March 2026 . The official bug description also lists fixes backported to 3.44.6 and 3.50.7. Check your distributor's patch status if it maintains an older release.

That bug is separate from the demonstration here. Leaving committed data in the WAL is normal behaviour, and a patched SQLite library does not make an uncoordinated raw copy reliable.

Make the final test concrete: reopen a separate copy of the backup and recover a record you know should be there. That checks the outcome the backup was created to deliver.

Sources and further reading

Documentation and examples checked on 28 October 2026.

• SQLite: Write-Ahead Logging and WAL file format
• SQLite: Online Backup API overview and API reference
• Python 3.12: sqlite3
• SQLite: VACUUM and VACUUM INTO
• SQLite: PRAGMA integrity_check and foreign_key_check
• SQLite: Backup, restore and journal-mismatch risks