Recovering corrupt Lightroom catalogs

Back in December, something disconcerting happened. My Adobe Lightroom 4 catalog wouldn't load. Disconcerting because I had something on the order of 50,000 photos in that catalog and recreating the whole shooting match would be a frightful chore. Indeed, I wasn't even quite sure how frightful that chore would be. 

However, I keep good backups in various forms and I was able to find a recent backup of my catalog that was happy enough to load. (The catalog does not include the photos themselves but it does include much of the information related to their organization and other metadata.)

But the problems didn't go away. And, today, even after a migration from a Windows system to a new Mac Mini, they came back in spades. I simply couldn't make a backup of a catalog from within Lightroom, which also refused to optimize it. I could pull in a somewhat earlier backup of the catalog through the Mac's Time Machine backup program, but there was obviously something deep wrong here. And Lightroom's repair catalog function refused to do anything useful.

As it turns out though, hat tip to Stephen Shankland for the info, Lightroom uses a SQLite3 database which has various tools one can use to fix corrupted databases.

The first thing I did was download SQLite3 and SQLite3_analyze. 

SQLite3_analyze confirmed there was indeed some sort of problem. "database image is malformed" it told me. Some searching never did tell me what that means exactly but, in any case, it confirmed there was a problem with the database.

The real find came from Gerhard Strasse's blog. Read the blog but I just want to add a few comments.

Basically the steps are pretty simple.

First you dump the existing catalog into a text file as a bunch of SQL commands:

echo .dump | ./sqlite3 ~/lightroom_catalog.lrcat > ~/lightroom_catalog.sql

then, in theory, you can just suck that text into a new database file with:

./sqlite3 -init ~/lightroom_catalog.sql ~/lightroom_catalog_restore.lrcat

The blog notes that you may get an error or two (i.e. the duplicate keys or whatever that were causing the database issue) but it should work. It didn't in my case. (lightroom_catalog_restore.lrcat was a zero byte file on the first try.) The secret for me was at the end of the comments.

Reader Chairat Juengmongkolwong noted that if, after the first step, you go into the text file (lightroom_catalog.sql in this case) and replace the last line that says:

ROLLBACK; — due to errors

with the line:

COMMIT TRANSACTION;

And THEN do the 

./sqlite3 -init ~/lightroom_catalog.sql ~/lightroom_catalog_restore.lrcat

it should work. Which it did for me. I never did see any errors so I'm not sure what the problem was. Hopefully, it is fixed now. 

Obviously, if you've never touched a Unix/Linux command line this is probably a bit intimidating and you'll probably need to enlist some help. Make copies and work on backups!  This process can doubtless be done on Windows as well although the details will be a bit different. The terminal console on a Mac is a Unix command line. I'd also note that if you have a very large catalog—mine is about 500MB—a lot of text editors won't be able to handle it. I ended up downloading and installing Vim which handled what a couple of others couldn't. 

Another possible  option, BTW, is to fire up a new catalog and import your existing (corrupt) catalog although this will cause you to lose any publishing services that you have setup and possibly other things as well. However, when I did this, it only imported a quarter or so of my photos. (Presumably it quit at the spot of the error.)

My configuration was a Mac Mini running Mountain Lion and Lightroom 4.3.

The one other thing I might add is that, given this is a fairly straightforward procedure, and one that is fairly standard for this particular SQLite database error as far as I can tell (whether for Lightroom or otherwise), it's unclear why it isn't embedded within Lightroom's own corrupt database recovery process.