How to repair an SQLite databaseUncategorized 22 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.
Pingback: Overflow on Mac OS X crash! | Cetrasoft
Pingback: AdamW on Linux and more » Blog Archive » Fixing a broken Firefox Awesomebar (places.sqlite repair)
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
puppy vaccination schedule
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
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. 🙂
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
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
I tried a tool which has successfully repaired my SQLite database file.
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/
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.
Also take a look at this tool: http://www.dbpros.org/sqlite/
Thank yo so much Nancy Roy. The software worked for me 🙂
You can also try the SQLite Recovery Utility.
Thank you for your article on How To Repair An SQLite Database