The biggest risk of any type of database file is corruption. Sometimes you can fix it, but too often the only way to recover from a corrupted file is to restore a backup from before the problem showed up and rebuild or recreate anything lost. For full database servers there are ways to minimize these problems, but for personal catalogs not so much. Major corruption let’s you know, often with a corrupt file message when starting the program. When the corruption is subtle you may not know it until it’s too late to easily recover.
I do a lot of photography and organize my work using Adobe Lightroom. At heart the Lightroom catalog is a specialized database storing information about the photos and the data you’ve attached to them. I found myself seeing an odd error whenever I would take an image into Photoshop for editing, the edited photo would not show in Lightroom as it should. After searching the Internet and talking with Adobe support, I confirmed the catalog was the problem.
I now faced the prospect of either creating a new catalog, importing my photos, and then rebuilding lost data or rolling back to a several month old backup and redoing every import and edit since then. The later might not have been a bad option except I’d done quite a few of both the previous few weeks. Either way I’d have to hope I didn’t miss anything. Neither felt like a particularly good option.
I began to look for ways to possibly pull the data I couldn’t normally save, such as pick/reject flags, from one catalog to another. I knew there was an SDK to create plugins and tools to work with Lightroom and I began to think of something to export everything I cared about into something like an XML or CSV file and then import it again.
I had no luck finding an existing app or plugin to do this, but during my search I learned that the catalog file in fact is a database. It’s a fairly common database format known as SQLite. This led me to the hope that I could extract the data I wanted using database queries. All those years writing web apps looked to be about to pay off in getting my data from the corrupted catalog.
I found two articles on the web at http://gerhardstrasse.wordpress.com/2010/08/19/recover-from-a-corrupt-adobe-lightroom-catalog-file/ and http://www.simplyness.com/more-photography-tips/recover-corrupted-unrepairable-lightroom-3-catalog-with-sqlite.html. Neither of these articles worked perfectly for me, but did get me in the right direction.
Without diving too deep into the technical details, SQL databases are a fairly common database structure and SQL is the language used natively to create and manipulate those databases. The process described involved converting the database into a text file that contains a series of SQL commands that could then be used to create the database.
First I downloaded the command line tool to deal with SQLite databases from https://www.sqlite.org/download.html. I downloaded and unzipped the shell binary for Mac OS resulting in a program that could be run from the command line to manage a SQLite database. I move the sqlite3 binary to my home folder along with a copy of my catalog file leaving the original safely put away in case this didn’t work. I then used the following to dump out the contents of the database into a text file containing the SQL commands needed to create that database:
echo .dump | ./sqlite3 ~/Lightroom-3-Catalog.lrcat > ~/Lightroom-catalog.sql
The vertical bar (|) breaks this command into two parts. The first part takes the characters .dump and sends it as the input to the second part. The effect is the same as typing those commands after the second part of the line runs. The rest of the command executes the sqlite3 binary I downloaded giving it my catalog file as the database (and yes I’ve been using this catalog since Lightroom 3). The .dump command tells SQLite to display the text commands it would take to create the database. At the end the greater than sign then tells my computer to send that text to a file named Lightroom-catalog.sql instead of displaying them on the screen.
So I now had a huge text file instead of a unreadable catalog file. Some articles I read noted common errors seen in the SQL commands, but my scan of the data found nothing out of order. So now that I had a text file I wanted to create a new database using this command:
cat Lightroom-catalog.sql | ./sqlite3 ~/Lightroom-Catalog-Repaired.lrcat
This command is again split into two parts. The cat command takes the contents of the Lightroom-catalog.sql file we just created and normally sends them to the screen. As before though the vertical bar instead sends them as input to the command that follows the pipe. This command creates a new database with the name. In effect the entire contents of the 600+ MB text file is automatically typed in.
I moved the new catalog file back to my Lightroom folder and opened it. Behold everything was there and all looked good. Only problem I ran into was that when I next imported photos into Lightroom it saw the parent of the folder holding these new files as different than the original folder in spite of being the same. It made no sense to me, but was easily fixed by clicking on each subfolder and using the locate folder to get everything synced up.
It’s been a bit over a month now and all is still working well. Hope that helps anyone else running into this problem.