How to repair an SQLite database

Uncategorized , , 20 Comments

Today I have faced with a crashed SQLite database. Actually, I am not totally sure it was crashed, but the sqlite process had not shut down properly, and (after this) to select anything from it has taken more than an hour, anyway it was damn slow, and has eaten a lot of CPU time, and has created a huge journal file on every select. Then I got the idea, why not dump it and restore to another file? So I tried the following:

# echo ".dump" | sqlite old.db | sqlite new.db

(Here we give the command “.dump” to the SQLite with the old DB, and we redirect the output (the SQL dump itself) to another SQLite process with the new DB. It is the same as dumping to a file, and then restoring it, but it is much faster, and does not need a (probably) big dump file.)

Then the database had become 1.7GB from the original 2.7GB, so it had a lot of overhead before, and it is really fast now.

20 thoughts on “How to repair an SQLite database

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.