Recover A Corrupted Unrepairable Lightroom 3 Catalog With SQLite
July 17, 2011
I knew it!!!, that was the first thing that came to my mind after learning that lightroom’s catalog native format is a SQLite. I didn’t know it was SQLite, but I new that Lightroom is somehow using a database technology intended for small-scale applications maybe like SQL Server Express from Microsoft. Anyway, thanks to an old post from Gerhard Strasse, now I know how to repair my unrepairable corrupted LR3 catalog.
For months I have been dealing with this catalog problem, it all started when I encountered an error in my lr3 scheduled backup, stating that there was an error reading the catalog file.
After restarting LR using the same catalog file, it attempted to repair the damage catalog, only to find out later that it cannot be repaired, good thing I have backup. I restored that, but when I tried to run a backup, same thing happened. And since then, I have been creating a backup manually by copying a the catalog file to a separate drive. I can’t even run integrity check and optimization on the catalog because it renders the catalog corrupted.
A week ago I noticed that the problem is getting worst. Aside from having a big 460mb catalog file, LR crashes frequently when I’m doing some editing, worst thing is, when I tried to restart LR3 using the same catalog, it’s already corrupted and cannot be use anymore, so that’s when I searched deeper and found SQLite to be the best solution. You can download SQLite here.
So if you find yourself with an unrepairable corrupted catalog, just follow this steps:
- Just to let you know, I’m on a Windows platform, if you’re on a mac, please read Gerhard Strasse’s post.
- Download SQLite.
- Extract all files in a folder. Mine is in c:\SQLite
- For simpler execution, Copy your corrupted catalog to the same folder, in this case copy it to c:\SQLite
- Open a DOS prompt or a Command prompt.
- Inside the command prompt change directory to c:\SQLite, the command is :
- cd \sqlite
- Here is the first of the two commands to execute to repair the catalog:
- echo .dump | sqlite3 YourCatalogFileName.lrcat > TempLRCatName.sql
- This command will extract all data from the database file into a text file with all the SQL commands needed to recreate the database. After exporting all the data, it will create a new database and use “.lrcat” as the file name’s extension. gerhardstrasse.wordpress.com.
- 2nd command which will initialize and create a new lr3 database file .lrcat.
- sqlite3 -init TempLRCatName.sql NewCatalogFileName.lrcat
- After the execution of the above command I got this message:
- Error: near line 6961135: database disk image is malformed.
After that, I copied the newly created Lightroom Catalog back to it’s folder and started it (still crossing my fingers), wallah!!!! Everything is back to normal. I tried to run my scheduled regular backup and it was successful. Looks like everything is back to normal. It took me 15 mins to repair the catalog, and oh before I forget, the old catalog that used to be 460mb was now down to 221mb. SQLite didn’t only repair the catalog, it optimized it and removed some fragmentation.
If you have any questions regarding this post, you can post a comment below or send me an email anytime. Thanks