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;
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:
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