Backup and Attach

Never cp a live database

A SQLite database is not one file while it is in use. Copying it with cp, rsync, tar, or a filesystem snapshot while any connection can write produces a copy that is either missing recent commits or outright corrupt.

ls app.db*
# app.db  app.db-shm  app.db-wal        <- three files, not one
cp app.db backup.db                     # WRONG: loses everything still in the -wal

Why it breaks, in both journal modes:

- In WAL mode, committed transactions live in app.db-wal until a checkpoint moves them. Copying only app.db silently drops them. Copying all three files without coordination can capture a torn -wal, and the -shm is shared memory whose contents are meaningless to another process. - In rollback-journal mode, a copy taken mid-transaction captures partially written pages plus a -journal that a restore will roll back to a state you did not intend. - cp is not atomic. A page written after cp read it, but before cp finished, gives you a file that no consistent transaction ever produced.

There is exactly one safe way to copy with the filesystem, and it is more trouble than it is worth: hold a lock over the whole copy so nothing can write.

# Only safe because the writer is blocked for the entire duration
sqlite3 app.db "BEGIN IMMEDIATE; .shell cp app.db backup.db" ".quit"

Use one of the real mechanisms below instead. All of them are safe against concurrent writers.

VACUUM INTO, the modern online backup

VACUUM INTO 'file.db' (3.27+) is one statement that produces a complete, compacted, already-checkpointed copy. It is the recommended backup for almost every case.

VACUUM INTO 'backup.db';
VACUUM INTO '/backups/app-' || strftime('%Y%m%dT%H%M%SZ','now') || '.db';
VACUUM two INTO 'two-backup.db';     -- back up an attached database by name

sqlite3 app.db "VACUUM INTO '/backups/app.db';"
D=$(date -u +%Y%m%dT%H%M%SZ)
sqlite3 app.db "VACUUM INTO '/backups/app-$D.db';"

What you get and what to watch for:

- A single file with no -wal and no -shm, safe to cp, gzip, or upload. - Defragmented and smaller, because it is a fresh rebuild rather than a page copy. - It runs inside a read transaction, so it takes a consistent snapshot and does not block writers. - It refuses to overwrite, which is a feature rather than an annoyance.

Error: output file already exists

- It cannot run inside a transaction, and it needs free space for the whole copy. - The destination path is resolved by the SQLite process, so a relative path is relative to that process's working directory. - A VACUUM INTO copy has a fresh page layout, so if any table lacks an INTEGER PRIMARY KEY its implicit rowids may change.

# Verify the backup, always
sqlite3 /backups/app.db "PRAGMA integrity_check;"          # -> ok
sqlite3 /backups/app.db "SELECT count(*) FROM users;"
gzip -9 /backups/app.db                                     # now safe to compress

.backup and .dump

The CLI offers two more, with different tradeoffs.

sqlite3 app.db ".backup backup.db"          # binary, uses the online backup API
sqlite3 app.db ".backup two two-bk.db"      # an attached database by name
sqlite3 app.db ".dump" > dump.sql           # portable SQL text
sqlite3 app.db ".dump users posts" > two.sql   # selected tables only
sqlite3 app.db ".save compact.db"           # VACUUM INTO under another name
sqlite3 corrupt.db ".recover" > salvage.sql # after real corruption

.backup uses the online backup API, which copies page by page and restarts if a writer changes the file mid-copy. On a database written to constantly it can take a long time or never finish, which is the main reason VACUUM INTO is now preferred.

.dump is the choice when the backup has to be readable, diffable, or restorable into a different SQLite version. It opens with PRAGMA foreign_keys=OFF; and a BEGIN TRANSACTION;, which is what lets it emit tables in any order.

sqlite3 app.db ".dump" | head -4
# PRAGMA foreign_keys=OFF;
# BEGIN TRANSACTION;
# CREATE TABLE a(id INTEGER PRIMARY KEY, v TEXT);
# INSERT INTO a VALUES(1,'x');

Restoring, in both directions:

# Text dump into a brand new database
sqlite3 new.db ".read dump.sql"
sqlite3 app.db ".dump" | sqlite3 fresh.db          # one pipeline, no temp file
gzip -dc dump.sql.gz | sqlite3 restored.db

# Binary file into an existing connection
sqlite3 app.db ".restore backup.db"
sqlite3 app.db ".restore two two-bk.db"

.restore overwrites the current database from a file, which is the reverse of .backup. It is destructive, so it is the one to double-check before pressing enter.

For a compressed streaming backup:

sqlite3 app.db ".dump" | gzip -9 > "app-$(date -u +%F).sql.gz"
sqlite3 app.db "VACUUM INTO '/tmp/snap.db'" && gzip -9 -c /tmp/snap.db > snap.db.gz

Checkpoint before you archive

If you are going to move files around, empty the WAL first so there is nothing left outside the main file.

PRAGMA wal_checkpoint(TRUNCATE);

# A cleanly closed, checkpointed database really is one file
sqlite3 app.db "PRAGMA wal_checkpoint(TRUNCATE); PRAGMA integrity_check;"
ls -la app.db*

A checkpoint can return busy if a reader is holding an old snapshot, and it does not change the fact that a new writer may append to the WAL a millisecond later. So use it as a tidy-up step, not as a substitute for VACUUM INTO.

ATTACH DATABASE

ATTACH adds another database file to the current connection under a name, and from then on you address its tables as name.table. That is what makes cross-database queries and inserts possible in a single statement and a single transaction.

ATTACH DATABASE 'other.db' AS two;
ATTACH DATABASE 'file:ro.db?mode=ro' AS ro;   -- URI form, read-only
ATTACH ':memory:' AS mem;                     -- a private in-memory database
ATTACH '' AS tmp;                             -- a temporary file, deleted on detach
DETACH DATABASE two;

Every connection always has main and temp. PRAGMA database_list shows what is attached, with resolved paths.

PRAGMA database_list;
-- 0|main|/data/at1.db
-- 2|two|/data/at2.db

Cross-database work, all of which is ordinary SQL once the attachment exists:

-- Query across files
SELECT * FROM main.a UNION ALL SELECT * FROM two.b;
SELECT u.email, l.action
FROM main.users u JOIN archive.audit_log l ON l.user_id = u.id;

-- Copy a table into another file
CREATE TABLE two.copy AS SELECT * FROM main.a;
INSERT INTO two.b SELECT id, v FROM main.a;

-- Move old rows to an archive file, atomically
BEGIN IMMEDIATE;
  INSERT INTO archive.events SELECT * FROM main.events
    WHERE at < datetime('now','-90 days');
  DELETE FROM main.events WHERE at < datetime('now','-90 days');
COMMIT;

-- Diff two files
SELECT 'only in main' AS side, * FROM main.a
  WHERE id NOT IN (SELECT id FROM two.b)
UNION ALL
SELECT 'only in two', * FROM two.b
  WHERE id NOT IN (SELECT id FROM main.a);

That archive move is genuinely atomic. A single transaction on a connection spans every attached database, so either both the insert and the delete land or neither does. The one exception is main in WAL mode plus attachments in rollback-journal mode, where the commit cannot be made atomic across both and SQLite falls back to committing them in sequence.

-- Same-shape tables in two files, compared by content
SELECT count(*) FROM (
  SELECT * FROM main.a EXCEPT SELECT * FROM two.b
);

ATTACH limits and gotchas

sqlite3 :memory: ".limits attached"
#             attached 10

Error: too many attached databases - max 10

The rest of the sharp edges:

- PRAGMA foreign_keys is connection-wide, but a foreign key may never cross databases. A REFERENCES in an attached file resolves only within that file. - A transaction is connection-wide too, so BEGIN covers every attachment. There is no way to commit one and roll back another. - A missing file is created empty rather than raising, so a typo silently attaches a new zero-table database. Check with PRAGMA database_list or attach with file:x.db?mode=rw so a missing file is an error. - page_size and encoding may differ between files, and a mismatched encoding makes the attach fail. - ATTACH is allowed inside a transaction on modern versions, but DETACH is not while the database is in use. - An unqualified table name resolves against main, then temp, then the attachments in order. Qualify names in any script that attaches, since an accidental shadowing is invisible. - temp.foo is per connection and vanishes on close, which makes it the right place for a staging table.

-- A staging table nobody else can see, and no cleanup to forget
CREATE TEMP TABLE staging(id INTEGER, email TEXT);
-- identical to
CREATE TABLE temp.staging(id INTEGER, email TEXT);

Replication with sqlite3_rsync

SQLite has no built-in replication. The closest official tool is sqlite3_rsync (3.47+), a separate binary that synchronizes one database to another over an SSH-style connection by exchanging page hashes, so it transfers only changed pages.

sqlite3_rsync app.db user@host:/data/app.db          # push
sqlite3_rsync user@host:/data/app.db local.db        # pull
sqlite3_rsync --verbose app.db /mnt/backup/app.db    # local to local

The important properties, from its documentation:

- Both databases must be in WAL mode, and the origin may be written to during the transfer. - It is one-directional. The replica is overwritten to match the origin, so it is a backup and read-replica mechanism, not multi-master sync. - It needs the sqlite3_rsync binary on both ends when going over SSH. - It is not shipped with the sqlite3 CLI on every platform. It is a separate build target in the SQLite source tree, and it is absent from the binary that comes with macOS, so check before depending on it.

command -v sqlite3_rsync || echo "not installed on this machine"

For genuine multi-writer replication you are outside plain SQLite, in the territory of Litestream (continuous WAL shipping to object storage), LiteFS, rqlite, or Turso. Those are separate projects with their own tradeoffs, and none of them is part of SQLite.

A backup routine that works

#!/bin/sh
set -eu
DB=/data/app.db
OUT=/backups
STAMP=$(date -u +%Y%m%dT%H%M%SZ)
SNAP="$OUT/app-$STAMP.db"

# 1. Consistent snapshot, safe while the app keeps writing
sqlite3 "$DB" "VACUUM INTO '$SNAP';"

# 2. Verify before trusting it
sqlite3 "$SNAP" "PRAGMA integrity_check;" | grep -qx ok

# 3. Compress and ship
gzip -9 "$SNAP"

# 4. Keep the last 14
ls -1t "$OUT"/app-*.db.gz | tail -n +15 | xargs -r rm --

Four rules behind that script. Snapshot with VACUUM INTO, never with cp. Always run integrity_check on the copy, because a backup you have not verified is a guess. Compress the snapshot rather than the live file. And test a restore periodically, since the only proof a backup works is a database rebuilt from it.

# The restore drill
gzip -dc /backups/app-20260915T120000Z.db.gz > /tmp/restore.db
sqlite3 /tmp/restore.db "PRAGMA integrity_check; SELECT count(*) FROM users;"