If you’ve worked with SQLite for any length of time from the command-line shell, you’ve probably noticed that a lot of its most useful commands start with a dot — .tables, .schema, .headers, and the one I want to focus on here: .dump. Unlike the commands I’ve covered elsewhere, .dump isn’t part of standard SQL at all. It’s a special command specific to the sqlite3 command-line tool, and it’s one of the most practical tools you’ll reach for when backing up, migrating, or inspecting a database. Let’s go through exactly what it does and how to use it well.
What .dump Actually Does
.dump outputs the entire contents of your database — every table’s schema and every row of data — as a series of plain SQL statements. Feed those statements back into a fresh SQLite database, and you’ve perfectly recreated the original. It’s essentially a portable, human-readable snapshot of your database, expressed entirely in SQL.
Let’s set up a small database to work with:
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE
);
INSERT INTO customers (name, email) VALUES
('Hina Baig', 'hina@example.com'),
('Tariq Chaudhry', 'tariq@example.com'),
('Sana Iqbal', 'sana@example.com');
Now, from within the SQLite CLI, run:
.dump
You’ll see output that looks roughly like this:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE
);
INSERT INTO customers VALUES(1,'Hina Baig','hina@example.com');
INSERT INTO customers VALUES(2,'Tariq Chaudhry','tariq@example.com');
INSERT INTO customers VALUES(3,'Sana Iqbal','sana@example.com');
COMMIT;
Notice it wraps everything in a transaction (BEGIN TRANSACTION / COMMIT), disables foreign key checks temporarily while restoring, recreates the exact table schema, and then reinserts every single row using explicit column values. Feed this back into a brand new, empty SQLite database, and you get an identical copy of the original.
Redirecting Output to a File
Printing the dump to your terminal is fine for a quick look, but the real value comes from saving it to a file. You can do this directly from the command line without even entering the SQLite shell interactively:
sqlite3 customers.db .dump > backup.sql
This creates a plain text file, backup.sql, containing everything needed to recreate customers.db from scratch. Because it’s just SQL text, it’s easy to inspect, version control, compress, or email to someone, in a way that the raw binary .db file often isn’t as convenient for (though copying the raw file works too, and I’ll touch on when to prefer one over the other later).
Alternatively, from within an interactive SQLite session, you can redirect output using .output:
.output backup.sql
.dump
.output stdout
The .output stdout line at the end is important — it resets output back to your terminal. If you forget it, every subsequent command you type will silently write to backup.sql instead of showing up on your screen, which can be confusing until you realize what happened.
Restoring from a Dump File
To rebuild a database from a dump file, create a new, empty database and pipe the SQL file into it:
sqlite3 restored.db < backup.sql
This reads through backup.sql line by line, executing each SQL statement, recreating the schema and repopulating every row exactly as it was when the dump was taken. If restored.db doesn’t already exist, SQLite creates it fresh.
Dumping a Single Table
You don’t have to dump the entire database every time. You can pass a table name (or a pattern) to .dump to restrict it to specific tables:
.dump customers
This produces just the CREATE TABLE and INSERT statements for customers, ignoring every other table in the database. This is useful when you only need to back up or migrate one part of a larger schema, rather than the whole thing.
You can also use wildcards with LIKE-style patterns:
.dump 'temp_%'
This dumps only tables whose names start with temp_, which is handy in databases with a lot of tables where you only care about a specific subset.
Comparing .dump Output to Copying the Raw File
A natural question is: why bother with .dump at all, when you could just copy the .db file directly with cp or a similar command? Both approaches have their place.
Copying the raw file is faster and preserves everything byte-for-byte, including things like the exact internal page layout. It’s the right choice for a quick, simple backup, especially of a large database, since it doesn’t require SQLite to read through and re-serialize every row as text.
.dump, on the other hand, produces portable, human-readable SQL. This matters in a few specific situations: if you’re migrating between different versions of SQLite that might have different internal file formats, if you want to version-control your database schema and seed data in something like Git (binary files don’t diff meaningfully, but SQL text does), or if you want to inspect exactly what’s in a database without needing a SQLite client to open the binary file.
I tend to reach for .dump when I want something readable and diffable, and I reach for a direct file copy when I just need a fast, exact backup and don’t care about human readability.
Using .dump for Schema-Only Exports
Sometimes you don’t want the data at all — just the table definitions, so you can recreate an empty version of the schema elsewhere. Combine .dump with a bit of filtering, or use the related .schema command instead, which only outputs CREATE statements without any INSERT statements:
.schema
This gives you just the structural definition of every table, index, and trigger, with none of the actual row data. It’s the better tool when your goal is purely to document or recreate structure, rather than back up data.
A Practical Backup Script
Here’s a pattern I use often for taking timestamped backups of a SQLite database using .dump, run from a shell script:
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
sqlite3 production.db .dump > "backup_production_${DATE}.sql"
gzip "backup_production_${DATE}.sql"
This dumps the database to a timestamped SQL file and compresses it immediately, since SQL text tends to compress extremely well thanks to its repetitive structure. Running this on a schedule via cron gives you a rolling history of readable, restorable backups without much effort.
Common Pitfalls to Watch For
Forgetting to reset .output back to stdout. As mentioned earlier, if you redirect output to a file and forget to redirect it back, subsequent commands silently vanish into that file instead of appearing on screen. This has confused me more than once when I forgot I’d left output redirected.
Assuming .dump captures everything about a database’s runtime state. It captures schema and data faithfully, but things like the current value of PRAGMA settings that aren’t part of the schema itself, or any in-memory temporary tables, aren’t included, since those aren’t persistent parts of the database file to begin with.
Restoring a dump into a database that already has data. If restored.db already contains tables with the same names as what’s in your dump file, you’ll hit “table already exists” errors partway through. Always restore into a genuinely empty file, or explicitly drop conflicting tables first.
Not accounting for foreign key ordering. .dump outputs PRAGMA foreign_keys=OFF at the top specifically so that tables can be recreated and populated in any order without foreign key constraints getting in the way during the restore process, since dependent tables might be created before the tables they reference. Don’t manually strip that PRAGMA line out unless you’re confident about the insertion order yourself.
Treating a large dump file casually. For very large databases, .dump output can be enormous, since every single row becomes a full INSERT statement. Compress it, and be mindful of how long a restore will take, since executing millions of individual INSERT statements sequentially does take real time.
Best Practices Worth Adopting
Use .dump for backups you want to be portable, readable, and version-controllable — schema and seed data are perfect candidates for checking into Git.
Use a direct file copy for fast, routine backups of large production databases where speed and byte-for-byte fidelity matter more than readability.
Always test your restore process, not just your backup process. A backup you’ve never successfully restored from is not something you should trust.
Automate and timestamp your backups with a simple script, and compress the output, since SQL dumps compress very efficiently.
Use .schema instead of .dump when you only need structure, not data, to keep your exports focused and easy to review.
Wrapping Up
.dump is a deceptively simple command that solves a surprisingly wide range of problems: backups, migrations, version control of schema and seed data, and easy inspection of a database’s full contents in plain text. It’s not a substitute for a more sophisticated backup strategy on a large production system, but for the vast majority of SQLite use cases — which tend to be smaller, embedded, or single-file applications — it’s often all you need. Get comfortable with it, script it into your routine, and you’ll never be caught without a readable, restorable copy of your data.
