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.
[...] to random things and Dmitri for the tips! Read more from Mac Click here to cancel [...]
[...] with a bit of GoogleHelp, I got it sorted. Back up the ‘broken’ places.sqlite to places.sqlite.bak, run Firefox [...]
Many thanks!
This small pipe has recovered my crashed f-spot database in a Jiffy.
In my case command was:
echo .dump | sqlite old.db | sqlite new.db
without quotes. “.dump” would give me some sqlite command error.
Hi, i could you give me more information? thx in advance
wow..jejejejehehe
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? =)
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.)
Oh so clever….
Here is a good tool to fix sqlite database and repair sqlite database
http://www.sqliterecovery.com/how-it-works.html
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. :)