SQLite: back up a database running in WAL mode

Understand the file set

In WAL mode, recently committed changes may be in the -wal file rather than only in the main .db file. Copying one file while an application is writing is not a universal consistent-backup method. Do not delete WAL or SHM files to “repair” a live database.

Use the backup mechanism

This example uses Python’s standard module. It creates a uniquely named destination instead of overwriting a previous backup. The executing account needs access to the source and backup directory.

import sqlite3
from datetime import datetime, timezone
from pathlib import Path

backup_dir = Path("backups")
backup_dir.mkdir(exist_ok=True)
stamp = datetime.now(timezone.utc).strftime("%Y%m%dT%H%M%SZ")
source = sqlite3.connect("file:app.db?mode=ro", uri=True)
target = sqlite3.connect(backup_dir / f"app-{stamp}.db")
try:
    source.backup(target, pages=100, sleep=0.1)
finally:
    target.close()
    source.close()

The mode=ro option avoids accidentally creating an empty database after a path typo. Online backup can operate alongside the application, but heavy writes make runtime and free-space monitoring important.

Validate the copy independently

Run PRAGMA integrity_check; or the appropriate integrity test against the backup. An ok result does not prove the expected application data is present. Also check key record counts, schema version and whether a compatible application can open it. Credentials or encryption keys may require separate protection.

Treat recovery as a procedure

Stop processes using the destination database, preserve the current version for diagnosis and perform a controlled restore. Do not replace one file beneath a running process. Regularly test recovery in a separate directory: producing an archive alone does not establish that the service can be recovered.

Continue reading