SQLite | SQLite Commands | Database Dump (.dump)
A database dump outputs schema and data as SQL statements. This article explains how to use .dump.
To rebuild a database from a dump, use the .read command.
Using .dump
Use .dump to export a SQLite database. The syntax is as follows.
.dump? TABLE? ...
With no argument, it dumps the entire database. Specify a table name to dump only that table.
Create sample tables and data.
create table user (name text, old integer, address text);
insert into user values ('devkuma', 28, 'Seoul');
insert into user values ('kimkc', 22, 'Busan');
insert into user values ('araikuma', 32, 'Seoul');
insert into user values ('happykuma', 23, 'Seoul');
insert into user values ('mykuma', 23, 'Daejeon');
create index nameindex on user (name);
create table color (id integer, name text);
insert into color values (1, 'Red');
insert into color values (2, 'Blue');
insert into color values (3, 'White');
Run .dump on a database containing two tables and one index.
.dump
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE user (name text, old integer, address text);
INSERT INTO user VALUES('devkuma',28,'Seoul');
INSERT INTO user VALUES('kimkc',22,'Busan');
INSERT INTO user VALUES('araikuma',32,'Seoul');
INSERT INTO user VALUES('happykuma',23,'Seoul');
INSERT INTO user VALUES('mykuma',23,'Daejeon');
CREATE TABLE color (id integer, name text);
INSERT INTO color VALUES(1,'Red');
INSERT INTO color VALUES(2,'Blue');
INSERT INTO color VALUES(3,'White');
CREATE INDEX nameindex on user (name);
COMMIT;
sqlite>
The output contains CREATE TABLE statements for the schema and INSERT statements for the data. Running them recreates the database.
Specify user to dump only that table.
.dump user
sqlite> .dump user
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE user (name text, old integer, address text);
INSERT INTO user VALUES('devkuma',28,'Seoul');
INSERT INTO user VALUES('kimkc',22,'Busan');
INSERT INTO user VALUES('araikuma',32,'Seoul');
INSERT INTO user VALUES('happykuma',23,'Seoul');
INSERT INTO user VALUES('mykuma',23,'Daejeon');
CREATE INDEX nameindex on user (name);
COMMIT;
sqlite>
Writing a Dump to a File
Use .output before .dump to redirect output to a file.
Redirect output to dump.txt in the current directory. This also redirects query results.
.output ./dump.txt
sqlite> .output ./dump.txt
sqlite>
Output now targets dump.txt. Run .dump.
.dump
sqlite> .dump
sqlite>
Nothing appears on screen because the dump is written to the file.
The current directory now contains a new dump.txt file.
$ ls -al
total 5056
drwxr-xr-x@ 8 kimkc staff 272 11 4 01:24 .
drwxr-xr-x 9 kimkc staff 306 10 17 23:49 ..
drwxr-xr-x 24 kimkc staff 816 11 4 01:24 bak
-rw-r--r-- 1 kimkc staff 16384 11 4 01:14 dump.sqlite3
-rw-r--r-- 1 kimkc staff 538 11 4 01:21 dump.txt
-rwxr-xr-x@ 1 kimkc staff 691768 10 11 18:31 sqldiff
-rwxr-xr-x@ 1 kimkc staff 1152260 10 11 18:32 sqlite3
-rwxr-xr-x@ 1 kimkc staff 719796 10 11 18:31 sqlite3_analyzer
$
Open the text file to inspect the recorded dump.
$ cat dump.txt
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE user (name text, old integer, address text);
INSERT INTO user VALUES('devkuma',28,'Seoul');
INSERT INTO user VALUES('kimkc',22,'Busan');
INSERT INTO user VALUES('araikuma',32,'Seoul');
INSERT INTO user VALUES('happykuma',23,'Seoul');
INSERT INTO user VALUES('mykuma',23,'Daejeon');
CREATE TABLE color (id integer, name text);
INSERT INTO color VALUES(1,'Red');
INSERT INTO color VALUES(2,'Blue');
INSERT INTO color VALUES(3,'White');
CREATE INDEX nameindex on user (name);
COMMIT;
$
Restore screen output with .output stdout.
.output stdout
sqlite> .output stdout
sqlite>
A file may remain locked until output is restored or the CLI exits.