How to repair an SQLite database

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.

This entry was written by gheja , posted on Friday March 27 2009at 08:03 pm , filed under Uncategorized and tagged , , . Bookmark the permalink . Post a comment below or leave a trackback: Trackback URL.

13 Responses to “How to repair an SQLite database”

  1. [...] to random things and Dmitri for the tips! Read more from Mac Click here to cancel [...]

  2. [...] with a bit of GoogleHelp, I got it sorted. Back up the ‘broken’ places.sqlite to places.sqlite.bak, run Firefox [...]

  3. Many thanks!
    This small pipe has recovered my crashed f-spot database in a Jiffy.

  4. In my case command was:
    echo .dump | sqlite old.db | sqlite new.db

    without quotes. “.dump” would give me some sqlite command error.

  5. Hi, i could you give me more information? thx in advance

  6. wow..jejejejehehe

  7. I tried to write this in the terminal in my mac, but it said sqlite_old.db command not found… This wasn’t supposed to get to the mac os x terminal right? =)

  8. It should work fine, but you don’t need that underscore between the sqlite and old.db – and of course the old.db is the name of your original database :) (Or a copy of it as it is always a good idea (if not a must have thing) to work on a copy of your original, untouched database and keep the original one well separated.)

  9. Oh so clever….

  10. Here is a good tool to fix sqlite database and repair sqlite database

    http://www.sqliterecovery.com/how-it-works.html

  11. Wow. This SqliteRecovery does with 20 steps, what this one-line command does. And it even costs money … nice tool, really.

    Anyways … thanks a lot for the command. Saved me probably some hours today. :)

  12. Not so far I have found new cool tool to work with SQLite – Valentina Studio. Its free edition can do things more than many commercial tools!!
    I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview

  13. Thanks for the tip Victor, I was looking for something like this product lately.

Leave a Reply

i
north-supercharged