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.