sqlite3 .dump: Usage, Options & Common CI Errors
.dump emits the SQL statements needed to recreate a SQLite database.
sqlite3 .dump is the portable way to back up or version a SQLite database as text. It pairs with .read to restore, and is preferred over copying the binary file across machines.
What it does
The .dump meta-command writes a complete SQL script (schema + INSERTs, wrapped in a transaction) that reconstructs the database. The text output is diff-friendly and portable across SQLite versions and architectures, unlike the raw .db file.
Common usage
sqlite3 app.db '.dump' > backup.sql
sqlite3 app.db '.dump users' > users.sql # one table
sqlite3 new.db '.read backup.sql' # restore into a new file
sqlite3 app.db '.backup safe.db' # online binary backupOptions
| Item | What it does |
|---|---|
| .dump | Emit SQL for the whole database |
| .dump <table> | Emit SQL for matching tables only |
| .read <file> | Replay a dump to restore |
| .backup <file> | Make a consistent binary copy (online) |
| .schema | Emit just the schema (no data) |
Common errors in CI
A .dump captured while another process is writing can be inconsistent - quiesce writers or use .backup for an online-consistent binary copy. Restoring with .read into a database that already has the tables fails on "table already exists"; restore into a fresh file. Very large dumps are slow to replay because each row is an INSERT; for big databases the binary .backup is faster than .dump/.read.