SQLite Backup and Restore: .backup, VACUUM INTO, and the Online API

Why You Can't Just cp the File

A SQLite database is a single file, which makes it tempting to back up with a plain file copy. Sometimes that works. Often it doesn't.

Two things can go wrong:

• Another connection is mid-write when you copy. The destination file ends up with a half-applied transaction — corrupt on open.
• The database is in WAL mode (the default for most modern apps). Recent changes live in a separate database.db-wal file. Copy only the main file and you've silently lost data.

SQLite gives you proper tools for this. They handle locking, WAL contents, and concurrent writers without surprises. Reach for them instead of cp.

The .backup Command

The fastest way to back up a database from the CLI is the .backup dot command:

sqlite3 app.db
sqlite> .backup backup.db
sqlite> .quit

That writes a complete copy of app.db to backup.db. It works even if other processes are reading or writing the database — the backup API takes a series of small locks instead of one big one, copies pages incrementally, and retries pages that get modified during the copy.

The output is a fully usable SQLite database. Open it like any other:

sqlite3 backup.db
sqlite> .tables

You can also do the whole thing in one shell command, which is what most cron jobs end up looking like:

sqlite3 app.db ".backup '/var/backups/app-$(date +%Y%m%d).db'"

One file in, one file out. No dump/restore round trip, no SQL parsing — just pages copied at the storage layer.

VACUUM INTO for a Compacted Copy

VACUUM INTO is a related but different tool. It writes a freshly built copy of the database to a new file:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('first'), ('second'), ('third');
DELETE FROM notes WHERE id = 2;

VACUUM INTO 'notes-backup.db';

SELECT 'wrote backup' AS status;

The result is the same logical database, but rewritten from scratch — every page packed tight, no fragmentation, no leftover free pages from deleted rows. That makes the backup file as small as possible.

When to pick which:

• .backup — routine, frequent backups. Faster, plays nicely with concurrent writers, byte-faithful.
• VACUUM INTO — periodic snapshots where you also want a tidy, minimum-size file. Slower because it rewrites everything, and it takes a write lock on the source for the duration.

Both produce a valid .db file you can open immediately.

The Online Backup API From Application Code

Inside an application, you don't shell out to sqlite3. You use the online backup API your driver exposes. In Python's stdlib sqlite3, that's Connection.backup:

import sqlite3

source = sqlite3.connect("app.db")
dest = sqlite3.connect("backup.db")

with dest:
    source.backup(dest)

source.close()
dest.close()

The backup method copies pages from source into dest while other connections keep working. You can also pass pages= to copy in chunks and progress= to receive a callback — useful for large databases where you want to throttle the copy or display progress.

Most drivers in other languages expose the same C API (sqlite3_backup_init, _step, _finish) under a similar name. The shape is always: open source, open destination, step through pages, finish.

Backups While the Database Is in Use

This is where SQLite quietly shines. Both .backup and the online backup API are designed for hot backups — the source database can be open and active the whole time.

What actually happens:

1. The backup grabs a shared lock and starts copying pages.
2. If a writer modifies a page that hasn't been copied yet, the backup notices and re-reads it.
3. The copy completes once every page is consistent.

You don't need to stop your app, kick out connections, or schedule downtime. On a busy database the backup may take a few extra cycles to converge, but it will converge. The destination file you end up with represents a consistent point-in-time snapshot.

One thing to know: if you're using WAL mode, run PRAGMA wal_checkpoint(TRUNCATE); occasionally so the WAL file doesn't grow unbounded. The backup itself handles the WAL correctly — this is just general WAL hygiene.

Restoring From a Backup

Restoring a SQLite database is unusually boring, which is the point. The backup file is a database. To use it, just open it:

sqlite3 backup.db
sqlite> SELECT COUNT(*) FROM notes;

To restore over a live database — for example, recovering after data loss — the safe sequence is:

1. Stop every process that has the database open.
2. Delete the existing app.db, app.db-wal, and app.db-shm files. Leftover WAL/SHM files from the old database will confuse SQLite when paired with the restored main file.
3. Copy your backup into place: cp backup.db app.db.
4. Restart your application.

The -wal and -shm files matter. If you skip step 2, SQLite may try to apply a stale WAL on top of the restored main file and you'll get either corruption or weirdly mixed data.

From inside the CLI, there's also a .restore command, the mirror of .backup:

sqlite3 app.db
sqlite> .restore backup.db
sqlite> .quit

This overwrites the contents of the connected database with the contents of backup.db. It uses the same online backup API in reverse.

.dump Is a Different Tool

You'll see references to .dump in older tutorials. It's not a backup in the same sense — it produces a SQL text file of CREATE and INSERT statements:

sqlite3 app.db .dump > app.sql

To restore, you replay the SQL:

sqlite3 new.db < app.sql

This is useful for migrating between SQLite versions, diffing schemas in git, or moving data into a different database engine. It's slower, larger, and lossier than .backup (custom collations, generated columns, and some pragmas can need extra care). For an actual backup of a working database, prefer .backup or VACUUM INTO.

A Sensible Backup Routine

For most apps, this combination works well:

• A scheduled .backup run — hourly, daily, whatever your data tolerance is. Cheap, fast, hot.
• A weekly VACUUM INTO to a separate path. Catches drift, gives you a compacted snapshot, exercises a different code path.
• A retention policy: keep the last N daily backups, the last M weekly ones. SQLite databases compress well, so gzip backup.db after the fact is worth doing.
• Occasionally restore one and run a few queries against it. An untested backup is a hope, not a backup.

# Daily, in cron:
sqlite3 /var/lib/app/app.db ".backup '/var/backups/app-$(date +%F).db'"
gzip "/var/backups/app-$(date +%F).db"

# Weekly:
sqlite3 /var/lib/app/app.db "VACUUM INTO '/var/backups/app-weekly-$(date +%F).db'"

Both commands are safe to run while the app is serving requests.

Next: PRAGMA Settings

Backups are one operational concern; tuning runtime behavior is another. SQLite exposes its knobs through PRAGMA statements — journal mode, synchronous level, cache size, foreign key enforcement. The next page walks through the ones worth knowing.