How to repair an SQLite database
Uncategorized 22 CommentsToday 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.
Pingback: Overflow on Mac OS X crash! | Cetrasoft
Pingback: AdamW on Linux and more » Blog Archive » Fixing a broken Firefox Awesomebar (places.sqlite repair)
Peter Pall
Many thanks!
This small pipe has recovered my crashed f-spot database in a Jiffy.
Duber
In my case command was:
echo .dump | sqlite old.db | sqlite new.db
without quotes. “.dump” would give me some sqlite command error.
Jensrud
Hi, i could you give me more information? thx in advance
puppy vaccination schedule
wow..jejejejehehe
John Eriksson
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? =)
kakaopor
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.)
roger
Oh so clever….
Samy
Here is a good tool to fix sqlite database and repair sqlite database
http://www.sqliterecovery.com/how-it-works.html
Bernd
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. 🙂
victor
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
gheja
Thanks for the tip Victor, I was looking for something like this product lately.
Pingback: How to: How do I unlock a SQLite database? | SevenNet
Pingback: Final AWARE results | Thesis Laurens Bolle
Nancy Roy
I tried a tool which has successfully repaired my SQLite database file.
http://www.stellarinfo.com/sqlite-repair.php
johnson Welch
Just take a look at the output of .dump, you’ll see how your database works and which entries produce errors or you can try Sqlite database Recovery tool http://www.sqliteviewer.com/database-recovery/
Jimmy McCrillis
Five years later, that command is still working. However, my version:
sudo service name_of_service stop
cp database.db database.db.bak #In case it dorks it up
echo .dump | sqlite3 database.db | sqlite3 database.db.new #Fingers crossed
mv database.db database.db.BROKE #Still keeping it in case things go poorly
mv database.db.new database.db #Put new one in place
sudo service name_of_service start #Fingers still crossed (Hard to type that way)
And it worked perfectly. Database went from about 309Mb to 284Mb.
Cheers!!
John Walker
Also take a look at this tool: http://www.dbpros.org/sqlite/
Kimberly
Thank yo so much Nancy Roy. The software worked for me 🙂
Mark dc
Hi,
You can also try the SQLite Recovery Utility.
Visit: https://www.sysinfotools.com/sqlite-database-recovery.html
Chris
Thank you for your article on How To Repair An SQLite Database