SQLite backup: How to dump a SQLite database

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

"How to 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 ...

Back to top

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

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.

Back to top

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 quick 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;
Back to top

How to dump an entire SQLite database

Finally, note that if you want to dump your entire SQLite database, and not just one table, you’d just 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.

Back to top

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.