Sqlite dump database

The last weeks I worked a lot with django the python web framework. And therefore to develope really fast, I normally use sqlite3 for beginning. Therefore I had to do a lot of dump and restore work and from time to time had to look in the history how I did it :) Maybe this short article helps me to remember the commands for a longer time :)

Dump sqlite/sqlite3

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

This will dump the information to screen. Thats fine for small databases. But if you have large databases, you need to dump your table to a file.

To dump your table to a file, you can use calls like this:

sqlite> .output coffee.sql
sqite> .dump coffees

This will dump the table coffees into coffee.sql.

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

Restore sqlite dump

To restore a sqlite dump on a unix system you have at least three possible options.

If you have a dump in a file called coffee.sql and a database called db.sqlite

First of all, you can use the cat command:

cat coffee.sql | sqlite3 db.sqlite

The second option is to insert it directly:

sqlite3 db.sqlite < coffee.sql

Or as third option you can use the inverse of .dump:

sqlite> .read coffee.sql

I normally use .dump and cat to reimport the dump.

comments powered by Disqus