Repairing a broken Lightroom catalog

This is a short translation of my blog article originally written in German.
I want to show how to repair a broken catalog that Lightroom itself cannot repair. This procedure worked for me once, and it may work for you. Please make a backup of your defect catalog befor you try to repair it.

My catalog was broken in a way that even Windows could not make backup of the catalog. Therefor the first step is to make a valid copy of the catalog. Open the Windows command line (cmd) and type:

type broken_catalog_name > new_catalog

After having a copy of the defect catalog I tried if Lightroom can read and repair the catalog. But Lightroom faild.
The Lightroom catalog is a SQLite database and in the next step I tried to repair this database.

Recovery of a broken SQLite database

First create a database dump:

echo .dump | ./sqlite3 new_catalog > new_catalog.sql

or

./sqlite3 new_catalog .dump > new_catalog.sql

The result is huge textfile with all information stored in the Lightroom catalog. In the next step I tried to create a new SQLite database (Lightroom catalog) with the following command:

./sqlite3 –init neuer_Katalog.sql Katalog.lrcat

SQLite failed to create the new database but wrote some interesting error messages. These error messages told me in which line of my text files are errors. All I have to do was to edit a 2.1 GB text file! With Pilot Editor Lite I was able to fix the errors in the textfile and created a new SQLite database:

./sqlite3 –init neuer_Katalog.sql Katalog.lrcat

I tried to open the catalog (database) in Lightroom but Lightroom said that the catalog was not valid. Therefore I created a new empty Lightroom catalog in Lightroom and used this catalog to import my database dump file with this comand:

./sqlite3 –init new_catalog.sql emptry_LR_catalog.lrcat

There were a few error messages like:

Error: near line 85415381: index index_AgRemotePhoto_collectionRemoteId already exists

which could be ignored.
I tried to open the emptry_LR_catalog.lrcat in Lightroom and Bingo! Lightroom was able to open my catalog and nearly all my work was still there.

Final works

I checked my last catalog backup and exported from my new empty_LR_catalog.lrcat all changes since the last backup. I imported these changes in my catalog backup, optimized the catalog and made a new backup.

The result

I only lost a few minor editings. All of my old editings are still available (from my last backup catalog). The repaired catalogs works without failers since more than one year now!