Recommended Free Tools
Use Python’s built-in sqlite3.Connection.backup() method to make a consistent copy of a live SQLite database, then schedule a script that uses it with cron. Validate each copy with PRAGMA integrity_check, and periodically test a restore: a scheduled job is only useful if its backups can be recovered.
Why use SQLite’s backup API instead of copying the database file?
Python’s standard-library sqlite3 module exposes SQLite’s online backup mechanism through Connection.backup(target, ...). It is designed to copy a database while other clients can access it. SQLite describes the completed destination as a bit-wise identical copy of the source as it was when the copying commenced. The source is read as needed during the copy rather than held continuously for its entire duration. See the SQLite Online Backup API and Python’s Connection.backup() documentation.
As an Amazon Associate I earn from qualifying purchases.
A plain file copy of a live database is especially risky when SQLite is using write-ahead logging (WAL). In that mode, recent database state can reside in a separate -wal file; copying only the main database file can omit transactions or produce a damaged copy. SQLite says the WAL file is part of the database’s persistent state while present. Do not try to make a live copy safe by manually copying or deleting -wal or -shm files. Use the backup API, or use a coordinated shutdown or tested snapshot procedure. See SQLite Write-Ahead Logging.
Build a backup script that validates its output
Save this as /usr/local/sbin/backup_app.py, adapting the source path, destination directory, permissions, and policy to your application. The example writes to a fixed “latest” filename for simplicity; consider publishing validated copies under timestamped names for recovery history.
#1 Best Overall
#!/usr/bin/env python3
from pathlib import Path
import sqlite3
source = Path("/var/lib/myapp/app.sqlite3")
backup_dir = Path("/var/backups/myapp")
backup_dir.mkdir(parents=True, exist_ok=True)
destination = backup_dir / "app-latest.sqlite3"
def progress(status: int, remaining: int, total: int) -> None:
copied = total - remaining
print(f"backup progress: {copied}/{total} pages; status={status}")
with sqlite3.connect(source) as src:
with sqlite3.connect(destination) as dst:
src.backup(dst, pages=256, progress=progress, sleep=0.25)
with sqlite3.connect(destination) as check:
result = check.execute("PRAGMA integrity_check").fetchone()
if result != ("ok",):
raise RuntimeError(f"backup integrity check failed: {result!r}")Connection.backup() was added in Python 3.7. Confirm that the interpreter available to the cron account supports it. The pages argument sets how many pages are copied per iteration; sleep sets a pause between attempts. The example uses batches of 256 pages and a 0.25-second pause as configurable choices, not performance guarantees. Smaller batches can yield more often, but the practical effect depends on database size, contention, and host resources. The default pages=-1 copies the whole database in one step. See Python’s backup API reference.
Publish only a completed, checked backup
For a production workflow, write to a temporary destination on the same filesystem, run the integrity check against it, and rename it to the final name only after validation succeeds. This reduces the chance that a failed run leaves a partial file presented as the latest usable backup; it is an operational safeguard, not a guarantee made by the SQLite API. Decide how successful completion is recorded, ensure the cron user owns or can write the destination, and remove old copies only after a new copy passes validation.
Choose a retention and recovery policy
A single overwritten file cannot recover an earlier state if the latest backup is unusable or includes an unwanted change. Retain timestamped copies according to how much recent data the application can afford to lose, how long restoration takes, the database’s change rate, and available storage. For recovery after host loss, keep a suitable copy somewhere other than that host. SQLite’s documentation does not prescribe retention periods or a storage provider.
Free tools Windows power users keep installed
One-click scans. No signup required.
Schedule the script with cron
Install a user crontab for an account that can read the database and write the backup directory. For an illustrative daily run at 02:15, use:
15 2 * * * /usr/bin/python3 /usr/local/sbin/backup_app.py >> /var/log/myapp/sqlite-backup.log 2>&1Replace /usr/bin/python3 with the absolute path to the interpreter used by the deployment, including a virtual-environment interpreter if applicable. Create the log directory and file with permissions that let the cron user append to them.
- Open the crontab for the account that should run the job:
crontab -e. - Add the schedule line, adjusting its time and paths for the host.
- Save the crontab, then check the installed entries with
crontab -l. - Inspect the configured log after a scheduled run and confirm that a new validated backup exists.
Cron runs a command as the owner of the relevant crontab and supplies a limited environment. Do not rely on an interactive shell’s current directory, PATH, or environment variables; use absolute paths and explicitly configure logging. Cronie documents /bin/sh as the default shell and sets LOGNAME and HOME from the account. Its MAILTO setting controls where command output is sent when mail is configured. Behavior can vary by cron implementation, so check the manual installed on your Linux distribution. See the Cronie crontab manual.
Set the schedule from recovery needs
The 02:15 example is not a universal recommendation. Choose a cadence based on the maximum tolerable loss of recent writes, and allow enough time for the copy and its validation to finish. Cron uses the machine’s configured time context; Cronie also documents CRON_TZ for a per-crontab timezone. If a run could overlap the next scheduled run, or someone may start it manually at the same time, consider a lock such as flock, after confirming it is installed and the cron user can write the lock file. This is a concurrency precaution, not a requirement of SQLite’s backup API.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Verify the backup and practice recovery
The script checks the destination using PRAGMA integrity_check. SQLite documents that the result is ok when no problems are found; otherwise it reports issues. See SQLite’s integrity_check documentation.
Best Value
An integrity check does not establish that the application has every record users expect or that the application can use the restored database correctly. Periodically copy a backup to a separate test location, open it with the same application and runtime, check representative records, and exercise relevant application behavior. Document the restore steps and measure how long a recovery takes. Treat that restore drill as a separate check from the integrity test.
When another copy method may fit
For a live database, the Python online backup API is the direct choice for this workflow. A coordinated shutdown or filesystem snapshot can suit a system with a tested, documented snapshot process. SQLite also documents VACUUM INTO as a way to create a consistent copy. Whichever method you choose, account for WAL state and verify that the resulting file can be restored.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
