Learning Center Draft

How to Back Up and Restore SQLite App Data Safely

Handle SQLite backups carefully so you capture consistent data, avoid corrupted copies, and can restore small self-hosted apps with confidence when something goes wrong.

BackupsSQLiteRecovery-first

What you learn

How SQLite stores data, why copying the raw file at the wrong moment can be risky, how to make safer backups, and how to test a restore before a real incident.

Best for

Single-node apps, hobby projects, internal tools, and self-hosted services that use a local SQLite database instead of Postgres or MySQL.

Risk to watch

SQLite looks like “just one file,” but write-ahead logging and active writes mean careless file copies can miss data or produce an inconsistent snapshot.

Before you begin

A host or containerized app that uses SQLite, and access to the database file path.
Enough free disk space to create a temporary backup copy.
The sqlite3 CLI installed if possible.
A place to store backup copies away from the live app directory.

SQLite is a great fit for many small services because it is simple, fast, and self-contained. But that simplicity can fool people into unsafe backup habits. If the app is actively writing while you copy the database files, the backup may not be as consistent as you think, especially if write-ahead logging is involved.

Expected outcome: By the end, you will know how to create a consistent SQLite backup, verify it, restore it to a test location, and automate the process without treating the database like an ordinary document file.

Step 1: Understand why SQLite backups need a little care

SQLite often stores data in a main .db file, but depending on its mode it may also use:

-wal files for write-ahead logging
-shm files for shared memory

If an app is running and writing to the database, copying only the main file may miss recent committed data or capture a bad moment mid-write. That is why safer methods matter.

The two beginner-friendly safe patterns are:

Use the SQLite backup command through sqlite3, which creates a consistent copy while the database is in use.
Stop the app briefly, then copy the database files while the app is fully quiet.

For most self-hosted apps, the first method is the nicest when the sqlite3 CLI is available and you can access the database file.

Step 2: Find the database file and current journaling mode

Locate the database file in your app config, volume mount, or container path. Common examples:

/srv/myapp/data/app.db
/var/lib/myapp/db.sqlite3
./data/database.sqlite

Check whether related files exist:

ls -lah /srv/myapp/data

Inspect the journaling mode with the SQLite CLI:

sqlite3 /srv/myapp/data/app.db 'PRAGMA journal_mode;'

If you see wal, expect a -wal file during active writes. That is normal. It just means you should not assume a raw copy of the main file alone is always enough.

If the app runs in Docker, you may need to execute the command inside the container or against the mounted host path:

docker compose exec app sqlite3 /app/data/app.db 'PRAGMA journal_mode;'

Step 3: Create a consistent backup

Preferred method: use SQLite’s built-in backup command.

mkdir -p ~/sqlite-backups
sqlite3 /srv/myapp/data/app.db ".backup '$HOME/sqlite-backups/app-$(date +%F-%H%M%S).db'"

This asks SQLite to produce a consistent backup copy.

To compress it afterward:

gzip ~/sqlite-backups/app-$(date +%F-%H%M%S).db

If the app is inside Docker, one safe pattern is:

docker compose exec app sh -lc 'sqlite3 /app/data/app.db ".backup /app/data/app-backup.db"'
docker cp myapp-app-1:/app/data/app-backup.db ./app-backup.db

Alternative method: stop writes briefly, then copy files.

docker compose stop app
cp /srv/myapp/data/app.db ~/sqlite-backups/app-$(date +%F-%H%M%S).db
docker compose start app

If the database uses WAL mode and you use raw file copies while stopped, copy the related files too if they exist:

cp /srv/myapp/data/app.db* ~/sqlite-backups/

The brief stop method is often acceptable for low-traffic internal apps, but the SQLite backup command is usually cleaner and less disruptive.

Step 4: Verify and test a restore before trusting the backup

A backup becomes much more useful once you prove it opens cleanly. Start with an integrity check:

sqlite3 ~/sqlite-backups/app-2026-05-10-160000.db 'PRAGMA integrity_check;'

A healthy result should return:

ok

Now restore to a temporary location instead of overwriting production immediately:

mkdir -p /tmp/sqlite-restore-test
cp ~/sqlite-backups/app-2026-05-10-160000.db /tmp/sqlite-restore-test/app.db
sqlite3 /tmp/sqlite-restore-test/app.db '.tables'

If the app can run against a copied test database, point it at the restored file in a staging environment or temporary container and confirm expected records or settings appear.

Expected outcomes:

The backup file opens successfully.
PRAGMA integrity_check; returns ok.
You can inspect tables or application data in a test location.

Step 5: Automate careful SQLite backups

A simple shell script works well for many small deployments:

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="$HOME/sqlite-backups"
DB_PATH="/srv/myapp/data/app.db"
STAMP="$(date +%F-%H%M%S)"
OUT="$BACKUP_DIR/app-$STAMP.db"

mkdir -p "$BACKUP_DIR"
sqlite3 "$DB_PATH" ".backup '$OUT'"
gzip "$OUT"
find "$BACKUP_DIR" -type f -name 'app-*.db.gz' -mtime +14 -delete

Run it manually first. Then schedule it with cron or a systemd timer only after you verify the output.

If you need off-server storage, sync the resulting backup archive to a second machine or object storage after it is created. A local-only backup does not protect against total server loss.

Rollback and recovery notes

If an app upgrade or failed write corrupts the live SQLite database, restore carefully:

Stop the app so it stops writing.
Copy the current damaged files aside for later analysis.
Restore the known-good backup into the expected database path.
Start the app and verify its behavior.

Example:

docker compose stop app
mkdir -p ~/sqlite-recovery
cp /srv/myapp/data/app.db* ~/sqlite-recovery/
cp ~/sqlite-backups/app-2026-05-10-160000.db /srv/myapp/data/app.db
docker compose start app

If the app used WAL mode, make sure stale -wal or -shm files from the broken state do not conflict with the restored main file. When in doubt, restore the full matching set of database-related files or let the app recreate fresh companion files after a clean restore.

Troubleshooting common SQLite backup problems

The backup file exists, but the app says the database is malformed.
Run PRAGMA integrity_check;. If it fails, the backup may have been taken unsafely or the source database was already damaged.

I copied only the .db file and data is missing.
If WAL mode was active, recent committed data may have still been in the -wal file. Use the SQLite backup command instead of raw copying while the app is live.

The container does not have sqlite3 installed.
Use the host path if the database volume is mounted there, or install sqlite3 in a temporary admin container that can access the same volume.

The restored app starts, but some recent changes are gone.
That usually means the backup is older than expected or a copy was taken without including live WAL state. Review the backup method and schedule.

The backup script deletes too much.
Test retention commands with find ... -print before replacing that action with -delete.

Warning: Do not test your first restore by overwriting the only live database. Restore into a temporary path first, prove it works, then promote it if needed.

What to do next

Once SQLite backups are dependable, the next improvement is scheduling them and checking for failures automatically. Continue with How to Set Up Healthchecks for Backups and Cron Jobs.