Back Up an SQLite Database

When you are changing an application, a separate copy of its database gives you something to restore if needed. An SQLite backup is a copy you can open to inspect or use to rebuild the original.

In this piece, I’ll compare .backup and .dump, then show how to check a restored database with PRAGMA integrity_check.

TL;DR: SQLite Backup Methods

Use .backup for a binary copy of a live database, .dump for a readable SQL export, and VACUUM INTO for a compact snapshot. Copy the database file directly only after every connection has closed.

• For a live database, prefer the Online Backup API or VACUUM INTO.
• For a portable text export, use .dump and restore into an empty database.
• Check a restored copy with PRAGMA integrity_check.

What Is an SQLite Backup?

An SQLite backup is a separate copy you can open to inspect or use to rebuild the original. A binary backup preserves the database structure as an SQLite file, while a SQL dump stores statements that recreate tables and rows.

I use a binary snapshot when I need to open the copy directly in SQLite. A dump is useful when I need a text file that can be inspected or loaded into a new database. The dump is not a single database file, so the restore step must execute its statements in order.

In write-ahead logging (WAL) mode, committed changes may still be in a separate -wal file until SQLite checkpoints them into the main database. A live file copy can therefore miss data. See how SQLite WAL mode stores changes before handling those sidecar files.

How to Back Up and Restore an SQLite Database

The sqlite3 shell includes commands for creating a binary copy and exporting SQL. These examples use a sample database and check both restored copies with SQLite itself.

Step 1: Create a binary backup with .backup

Run .backup from the shell connected to the source. I created a sample table with two rows, then copied the database and checked its contents:

sqlite3 source.db '.read backup_demo.sql'
sqlite3 source.db '.backup backup.db'
sqlite3 backup.db 'PRAGMA integrity_check; SELECT count(*) FROM notes;'

Output:

ok
2

The integrity check returned ok, and the backup contains two rows. SQLite’s Online Backup API creates a consistent snapshot while the database is live. Applications can also copy incrementally, releasing the source read lock between batches.

Step 2: Export and restore a SQL dump

A dump is plain SQL that rebuilds the database. Save it to a file, then feed it to a new, empty database:

sqlite3 source.db '.dump' > dump.sql
sqlite3 restored.db '.read dump.sql'
sqlite3 restored.db 'PRAGMA integrity_check; SELECT count(*) FROM notes;'

Output:

ok
2

The export contains SQL statements rather than a byte-for-byte database file. Restore it to an empty destination because the dump includes table creation and row inserts that conflict with existing objects.

Step 3: Choose VACUUM INTO or a stopped-file copy

VACUUM INTO makes a compact, consistent copy of a live database. Choose a destination that does not already exist or is empty:

VACUUM INTO 'compact-backup.db';

An interrupted VACUUM INTO may leave an incomplete destination. Check the new file before relying on it, and see the SQLite VACUUM guide for more detail.

This command rebuilds the copy and removes deleted content from it. The Backup API can copy incrementally. A direct file copy is appropriate only when no connection has the database open.

WAL mode stores committed changes in a separate file, so copying just the main file while the application is running is unsafe. I use SQLite’s backup command for live files because it creates a consistent snapshot rather than relying on filesystem timing.

SQLite Backup Edge Cases

Choose the fix by the database state and the symptom. A separate destination avoids confusing an incomplete backup with the source.

Case	Symptom	Fix
Live database in WAL mode	Recent rows missing in a file copy	Use .backup or VACUUM INTO while SQLite is open
SQL dump restored over existing tables	Table already exists or key conflict	Restore into a new empty database
Stopped database	No connections open	Copy the database after close and retain required WAL state until SQLite closes cleanly

Do not delete a live database’s -wal file to make a copy look complete. SQLite uses it to read committed data, and the related -shm file supports coordination between connections. See when WAL checkpointing moves those changes.

A restore error about an existing table or conflicting primary key means the destination already contains data. Make a fresh empty file and load the dump there, then run the integrity check again.

Conclusion: Verify the Restore

A backup is useful only if it can be read back. Pick .backup for a live binary snapshot, .dump for SQL reconstruction, or VACUUM INTO for a compact live copy, then test the result with PRAGMA integrity_check.

FAQ

This FAQ answers two common backup questions.