Skip to main content

Database Dump

A dump is a SQL script representation of your database.

It is useful for migration, versioned backups, and human-inspectable recovery artifacts.

Core Concepts​

flowchart TD
  A[(source.db)] --> B[sqlite3 .dump]
  B --> C[(dump.sql)]
  C --> D[sqlite3 new.db < dump.sql]
  D --> E[(recreated database)]
ArtifactContains
.db file backupBinary database pages
.sql dumpCREATE + INSERT statements

Code Examples​

# Export full schema+data SQL script.
sqlite3 app.db ".dump" > app_dump.sql
Expected output
app_dump.sql created with SQL statements.
# Preview whether dump contains table creation statements.
sqlite3 ":memory:" ".read app_dump.sql" ".tables"
Expected output
Table list from dump appears, proving script is loadable.
# Recreate a database from dump.
sqlite3 restored_from_dump.db < app_dump.sql
Expected output
New database created from dump script.

SQLite-Specific Nuances​

SQLite Nuance

.dump is text-based and portable, but restore time may be slower than copying binary .db files for large datasets.

Common Pitfalls / Best Practices​

Pitfall

Assuming a dump file is valid without testing restore.

Best Practice

Periodically run a restore test from your latest dump in a temporary database.

Quick Challenge​

Create snapshot.sql from project.db, then recreate project_clone.db from that dump.

View Solution
sqlite3 project.db ".dump" > snapshot.sql
sqlite3 project_clone.db < snapshot.sql
Expected output
project_clone.db contains schema and rows from project.db.