Skip to content

How to back up a SQLite database

Copy it with .backup or VACUUM INTO while your app runs, never with cp, or back it up continuously to lose only seconds of writes.

Back up a database while your app runs

Each of these makes a consistent copy, even mid-write.

# One consistent copy, safe while your app writes
sqlite3 /var/lib/app/main.sqlite ".backup /backups/main-$(date +%F).sqlite"

Choose a method

Method Safe while your app writes Writes you can lose Restore to any moment
cp of the file No Everything, if the copy is broken No
sqlite3 .backup Yes Everything after the last run No
VACUUM INTO Yes Everything after the last run No
Disk or VM snapshot Yes, if it captures every file at once Everything after the last snapshot No
Continuous backup with Litebackup Yes About the last few seconds Yes

Why cp breaks a copy

cp reads the file in pieces while your app writes, so the copy can mix old and new data. SQLite's own guide, How To Corrupt An SQLite Database File, lists this first. In WAL mode the newest commits sit in the -wal file, so copying the main file alone misses them.

Copy the file only when no connection has it open.

Copy with .backup

The sqlite3 command copies a consistent database while your app runs. It reads the whole database every time: a 10 GB database means about 10 GB read and written per run.

Copy with VACUUM INTO

VACUUM INTO writes a compacted copy, and works from any SQLite driver. It rebuilds every table and index, so it takes longer than .backup on large files. The target file must not exist.

Use disk or VM snapshots

A volume snapshot captures the database and its -wal file at the same instant. SQLite opens it as after a power cut, with every committed write. Keep both files on the same volume. A restore brings back the whole disk.

Run copies on a schedule

Cron or a systemd timer gives you one copy per run. Anything written after the last run is lost with the machine, and you can go back only to the moments you copied. Litebackup and nightly backups covers the trade-off.

Back up continuously

Litebackup sends every change to your Litebackup server within seconds and keeps an encrypted copy in your bucket. You can restore any version you keep, and your app keeps using SQLite as it does now.

Run litebackup backup on a machine that joined your server (Get started), or call backup() from your code with the SDK for TypeScript or Go. --wait server returns once your server holds the first version.

Check a copy

Restore into a new file and check it before you put it in place:

sqlite3 /tmp/restored.sqlite "PRAGMA integrity_check"

It prints ok for a sound database. litebackup restore runs SQLite's quick_check for you and never overwrites a file.

Next time

Get started

Install your Litebackup server and back up a first database in minutes: get started, or see pricing.