SQLite backup: How to dump a SQLite database

SQLite dump/backup FAQ: How do I backup (dump) a SQLite database (or a SQLite database table)?

"How do I make a backup of a SQLite database?" is actually a really interesting question. Because a SQLite database is really just a file, if you truly want to make a backup of that database, it can be as simple as copying that file to a backup location.

But there is more to the story ...

SQLite `dump` command

If you want to make a SQLite database dump — which will give you the schema for all your database tables and data, in a text format — you can use the SQLite dump command from the SQLite command line.

For instance, if you have a database table named coffees, and want to make a dump of it, you can run this SQLite dump command:

sqlite> .dump coffees

However, be warned that this command dumps the information to your screen, so with my little example SQLite database, I see output on screen like this:

1|Colombian|7.99
2|French_Roast|8.99
3|Espresso|9.99
4|Colombian_Decaf|8.99
5|French_Roast_Decaf|9.99

But if you create a SQLite dump of a much larger database, you can be looking at SQL output for a long time.

Solution: Redirect SQLite dump output to a file

Assuming that you really want to dump your SQLite database to a file, you should precede your SQLite dump command with an “output” command to redirect output to a file first.

Here's a little example that shows the steps of how to dump a SQLite database to a file:

sqlite> .output coffee.sql

sqlite> .dump coffees

sqlite> .quit

Now if I look at the file coffee.sql, it will contain this output:

$ cat coffee.sql

BEGIN TRANSACTION;
CREATE TABLE coffees (
  id INTEGER PRIMARY KEY,
  coffee_name TEXT NOT NULL,
  price REAL NOT NULL
);
INSERT INTO "coffees" VALUES(1,'Colombian',7.99);
INSERT INTO "coffees" VALUES(2,'French_Roast',8.99);
INSERT INTO "coffees" VALUES(3,'Espresso',9.99);
INSERT INTO "coffees" VALUES(4,'Colombian_Decaf',8.99);
INSERT INTO "coffees" VALUES(5,'French_Roast_Decaf',9.99);
COMMIT;

How to dump an entire SQLite database

Finally, note that if you want to dump your entire SQLite database, and not just one table, issue the SQLite dump command without specifying a database table, like this:

sqlite> .dump

As a final note/reminder, a SQLite database is just a file, so another way to make a backup of the database is to just make a copy of that file.