Recover from a corrupt Adobe Lightroom catalog file

Please note: the described catalog repair method has been tested with Adobe Lightroom 2.x catalog versions only.

It happened to me a few days ago that Adobe Lightroom 2.7 (OS X 10.6) crashed while selecting a single photograph. Upon opening the program again I was told that my catalog file was corrupt and needed to be repaired.

At this point, NEVER EVER press the “Repair Catalog” button but press “Quit” and create a copy of the catalog file first! Locate your catalog file in Finder / Explorer / a shell and create a copy in a safe place on your computer or an external hard drive. The file’s extension will be “.lrcat”.

After you created the copy of your catalog file you may restart Lightroom and choose “Repair Catalog”. I don’t know what this action is doing to your catalog file but I ended up with another dialog telling me, the ctatalog can not be repaired at this time. (and any other time either 🙂

As for many others out there this was the time when I started using search engines to recover from that problem. Creating that catalog was a substantial effort and of course a current backup was unavailable. Apart from many references to a guy named Dan Tull from Adobe who alledgedly is able to fix corrupt catalog fles and commercial offers I was unable to find any post with a self-service How-To. I ended up opening the catalog file in a text editor and found to my surprise that the first bytes read “SQLite format 3”.

Hooray, searching the web for recovering from corrupt SQLite files yielded far more results than my previous approach. Yes, there are SQLite utilities and yes, you can dump and recreate a database from files. Being experienced with similar tasks in MySQL I decided to give it a shot.

First, download the command line utilities that fit your operating system from SQLite’s home page (http://www.sqlite.org). There are precompiled binaries available for a number of operating systems on their download page (http://www.sqlite.org/download.html). You’ll need at least the “sqlite3” command line interface to follow these instructions. There is an analyzer tool available as well that might be handy to investigate further problemes with a SQLite database file.

After you downloaded the files to your computer and extracted the “sqlite3” command line utility, fire up a shell / command window / terminal and locate the path where you saved the sqlite3 binary before. Then start extracting the data from the corrupt file:

echo .dump | ./sqlite3 ~/lightroom_catalog.lrcat > ~/lightroom_catalog.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, create a new database and use “.lrcat” as the file name’s extension

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

This command will initialize a new database file and read all data from the SQL file you created in the first step.

The output of this command when being used with my catalog file was

— Loading resources from /Users/rmuetze/lightroom_catalog.sql

Error: near line 1793455: PRIMARY KEY must be unique

For the time being I didn’t care but fired up Lightroom with the restored catalog file. And voila! all my precious photos appeared again. There was one photo in the preview pane that didn’t exist in the file system actually so I guess that entry was the culprit for the database “corruption” I experienced.

Good luck to everybody who may use these instructions to revive a formerly forsaken Lightroom catalog file!

Advertisements
  • Trackback are closed
  • Comments (42)
    • Katie Luther
    • October 12th, 2010

    Hello,
    I just had the same situation happen to me with my lightroom catalog file….VERY heart breaking….at least for a woman. 🙂
    I read your blog and am a little confused, as that I have never done any work with coding and this instruction kinda sent my head spinning.

    “After you downloaded the files to your computer and extracted the “sqlite3″ command line utility, fire up a shell / command window / terminal and locate the path where you saved the sqlite3 binary before. Then start extracting the data from the corrupt file:”

    Is there any possibility you would be willing to help me a little further? I am freelance photographer and I am praying HARD that this can work and restore my lost editing. 😦 So that I don’t have to call my clients and break the news that my deadlines are shot to pot.
    I much appreciate ANY help you could offer, and thank you in advance for your time!

    • Ross
    • January 27th, 2011

    Wow. Thank you very much.

    I just encountered a corrupted LR3 catalog tonight. I went all the way back in time in Time Machine and the earliest backup was also corrupt. The last catalog from my old PC was fine. Just typical that in the time that I was waiting for a backup disk for Time Machine to use, my catalog became corrupted and I only found out now.

    The sqlite restore worked a treat!

    • Mikhail
    • June 20th, 2011

    Worked as a charm! Thanks a lot!

    • Jordan
    • November 22nd, 2011

    Wow. Too good to be true. Appear to have lost color categorization but everything else seems to be intact at first glance. Thank you so much!

    • Thaulow
    • May 8th, 2012

    Strange! Tried this five times. Everytime it leaves an emty database when usin -init. Tried a little test db that worked, but the LRcat.sql is not working, and i’m not getting any error codes. Got a clue?

  1. Hi! Thanks for this page: I’m hoping really hard it will help me, but until now, no joy… I downloaded the files indicated, put them in the same folder as the lightroom catalogue in question, double-clicked on the sqlite3 file. the terminal opens and prompts me to write commands followed by ; . I dial the line you wrote to copy data to a new database and in return I get three dots at the prompt ( … ) But nothing else… I write it again, and again same result… No file is created (I of course changed the names of the catalogues accordingly to what they really are)… Any idea? Somehow I’m very much screwed if I can’t recover that catalogue… Because of course the backup was on a disk that also failed…
    Thanks for any help!
    Much appreciated!

    Matteo Indri

      • rmuetze
      • June 29th, 2012

      Hi! Do not double klick the tools. They’re command line utilitiies. Just fire up the shell from Utilities/Terminal, then use the “cd” command to change the directory to where you store your catalog *COPY* and type the commands exactly as they’re cited in the article. Let me know if you need more help.
      Cheers

      Ruediger

      • Hi! I erroneously answered in the topic and not replied to you… Thanks!

  2. Hi Ruediger! I much, much, much appreciate your help! I tried again, with the cd thing and this time the file lightroom_catalog.sql was created. Only it is of 52 bytes, while the lightroom catalogue is of 2.3Gb.
    I tried again in the root directory and this time the result was a lightroom_catalog.sql of 7 megs and a restore of zero byte with the following lines… Sorry for the long post…

    — Loading resources from /Users/matteoindri/lightroom_catalog.sql
    Error: near line 23073: no such table: main.Adobe_AdditionalMetadata
    Error: near line 23074: no such table: main.AgMetadataSearchIndex
    Error: near line 23075: no such table: main.Adobe_imageProperties
    Error: near line 23076: no such table: main.Adobe_libraryImageDevelopSnapshot
    Error: near line 23077: no such table: main.Adobe_libraryImageDevelopHistoryStep
    Error: near line 23078: no such table: main.AgLibraryKeywordImage
    Error: near line 23079: no such table: main.AgLibraryKeywordImage
    Error: near line 23080: no such table: main.Adobe_images
    Error: near line 23081: no such table: main.Adobe_images
    Error: near line 23082: no such table: main.Adobe_images
    Error: near line 23083: no such table: main.Adobe_images
    Error: near line 23084: no such table: main.Adobe_images
    Error: near line 23085: no such table: main.Adobe_images
    Error: near line 23086: no such table: main.Adobe_images
    Error: near line 23087: no such table: main.Adobe_images
    Error: near line 23088: no such table: main.AgLibraryKeyword
    Error: near line 23089: no such table: main.AgLibraryKeyword
    Error: near line 23090: no such table: main.AgLibraryKeywordSynonym
    Error: near line 23091: no such table: main.AgLibraryKeywordSynonym
    Error: near line 23092: no such table: main.AgLibraryCollection
    Error: near line 23093: no such table: main.AgLibraryCollection
    Error: near line 23094: no such table: main.AgLibraryCollectionImage
    Error: near line 23095: no such table: main.AgLibraryCollectionImage
    Error: near line 23096: no such table: main.AgLibraryCollectionContent
    Error: near line 23097: no such table: main.AgLibraryCollectionContent
    Error: near line 23098: no such table: main.AgLibraryPublishedCollection
    Error: near line 23099: no such table: main.AgLibraryPublishedCollection
    Error: near line 23100: no such table: main.AgLibraryPublishedCollectionImage
    Error: near line 23101: no such table: main.AgLibraryPublishedCollectionImage
    Error: near line 23102: no such table: main.AgLibraryPublishedCollectionContent
    Error: near line 23103: no such table: main.AgLibraryPublishedCollectionContent
    Error: near line 23104: no such table: main.AgLibraryIPTC
    Error: near line 23105: no such table: main.AgLibraryImportImage
    Error: near line 23106: no such table: main.AgSpecialSourceContent
    Error: near line 23107: no such table: main.AgLibraryFile
    Error: near line 23108: no such table: main.AgLibraryFile
    Error: near line 23109: no such table: main.AgLibraryKeywordPopularity
    Error: near line 23110: no such table: main.AgLibraryKeywordCooccurrence
    Error: near line 23111: no such table: main.AgLibraryKeywordCooccurrence
    Error: near line 23112: no such table: main.AgLibraryKeywordCooccurrence
    Error: near line 23113: no such table: main.AgPhotoComment
    Error: near line 23114: no such table: main.AgPhotoComment
    Error: near line 23115: no such table: main.AgPhotoComment
    Error: near line 23116: no such table: main.AgPhotoProperty
    Error: near line 23117: no such table: main.AgPhotoPropertyArrayElement
    Error: near line 23118: no such table: main.AgSearchablePhotoProperty
    Error: near line 23119: no such table: main.AgSearchablePhotoProperty
    Error: near line 23120: no such table: main.AgSearchablePhotoProperty
    Error: near line 23121: no such table: main.AgSearchablePhotoPropertyArrayElement
    Error: near line 23122: no such table: main.AgSearchablePhotoPropertyArrayElement
    Error: near line 23123: no such table: main.AgSearchablePhotoPropertyArrayElement
    Error: near line 23124: no such table: main.AgRemotePhoto
    Error: near line 23125: no such table: main.AgRemotePhoto
    Error: near line 23126: no such table: main.AgVideoInfo
    Error: near line 23127: no such table: main.Adobe_imageDevelopBeforeSettings
    Error: near line 23128: no such table: main.Adobe_imageDevelopSettings
    Error: near line 23129: no such table: main.Adobe_imageDevelopSettings
    Error: near line 23130: no such table: main.Adobe_namedIdentityPlate
    Error: near line 23131: no such table: main.Adobe_namedIdentityPlate
    Error: near line 23132: no such table: main.AgMRULists
    Error: near line 23133: no such table: main.AgHarvestedExifMetadata
    Error: near line 23134: no such table: main.AgHarvestedExifMetadata
    Error: near line 23135: no such table: main.AgHarvestedExifMetadata
    Error: near line 23136: no such table: main.AgHarvestedExifMetadata
    Error: near line 23137: no such table: main.AgHarvestedExifMetadata
    Error: near line 23138: no such table: main.AgHarvestedExifMetadata
    Error: near line 23139: no such table: main.AgHarvestedExifMetadata
    Error: near line 23140: no such table: main.AgHarvestedExifMetadata
    Error: near line 23141: no such table: main.AgHarvestedExifMetadata
    Error: near line 23142: no such table: main.AgHarvestedExifMetadata
    Error: near line 23143: no such table: main.AgHarvestedExifMetadata
    Error: near line 23144: no such table: main.AgHarvestedIptcMetadata
    Error: near line 23145: no such table: main.AgHarvestedIptcMetadata
    Error: near line 23146: no such table: main.AgHarvestedIptcMetadata
    Error: near line 23147: no such table: main.AgHarvestedIptcMetadata
    Error: near line 23148: no such table: main.AgHarvestedIptcMetadata
    Error: near line 23149: no such table: main.AgHarvestedIptcMetadata
    Error: near line 23150: no such table: main.AgHarvestedIptcMetadata
    Error: near line 23151: no such table: main.AgHarvestedIptcMetadata
    Error: near line 23152: no such table: main.AgInternedIptcCreator
    Error: near line 23153: no such table: main.AgInternedIptcCreator
    Error: near line 23154: no such table: main.AgInternedIptcCountry
    Error: near line 23155: no such table: main.AgInternedIptcCountry
    Error: near line 23156: no such table: main.AgInternedIptcLocation
    Error: near line 23157: no such table: main.AgInternedIptcLocation
    Error: near line 23158: no such table: main.AgInternedIptcState
    Error: near line 23159: no such table: main.AgInternedIptcState
    Error: near line 23160: no such table: main.AgInternedExifCameraModel
    Error: near line 23161: no such table: main.AgInternedExifCameraModel
    Error: near line 23162: no such table: main.AgInternedExifCameraSN
    Error: near line 23163: no such table: main.AgInternedExifCameraSN
    Error: near line 23164: no such table: main.AgInternedIptcJobIdentifier
    Error: near line 23165: no such table: main.AgInternedIptcJobIdentifier
    Error: near line 23166: no such table: main.AgInternedIptcCity
    Error: near line 23167: no such table: main.AgInternedIptcCity
    Error: near line 23168: no such table: main.AgInternedExifLens
    Error: near line 23169: no such table: main.AgInternedExifLens
    Error: near line 23170: no such table: main.AgHarvestedMetadataWorklist
    Error: near line 23171: no such table: main.AgHarvestedMetadataWorklist
    Error: near line 23172: no such table: main.AgLibraryImageXMPUpdater
    Error: near line 23173: no such table: main.AgLibraryImageXMPUpdater
    Error: near line 23174: no such table: main.AgPublishListenerWorklist
    Error: near line 23175: no such table: main.AgPublishListenerWorklist
    Error: near line 23176: no such table: main.AgLibraryImportImage
    Error: near line 23177: no such table: main.AgSpecialSourceContent
    Error: near line 23178: no such table: main.AgLibraryFolder
    Error: near line 23179: no such table: main.AgLibraryFile
    Error: near line 23180: no such table: main.AgPhotoProperty
    Error: near line 23181: no such table: main.AgPhotoPropertyArrayElement
    Error: near line 23182: no such table: main.AgSearchablePhotoProperty
    Error: near line 23183: no such table: main.AgSearchablePhotoPropertyArrayElement
    Error: near line 23184: no such table: main.AgPhotoPropertySpec
    Error: near line 23185: no such table: main.AgRemotePhoto
    Error: near line 23186: no such table: main.AgRemotePhoto
    Error: near line 23187: no such table: main.Adobe_images
    Error: near line 23194: no such table: main.Adobe_images

    Any idea? Of course lightroom says that the catalogue is not usable…

  3. Nothing 😦

      • rmuetze
      • July 12th, 2012

      Hi Matteo,

      which version of Lightroom are you using? This article is based on the 2.x release. I do not know, whether Adobe stuck with the file format in releases 3 and 4. Also, did you create a copy of the damaged catalog *first*? If yes, please try to create an compressed archive from it and tell me it’s size. I am sorry that my method did not work with your catalog so far.

      Regards

      Ruediger

      • Hi! I’m using Lightroom 4… And the original ltrm file is 2.59Gb: once compressed it becomes 2.21Gb. I did create *several* copies of the file, in different disks, just in case 😉

        I’ll wait for an illuminating idea… My (short term at least) future is in your hands…

        Thanks,

        Matteo

        • Mikhail
        • July 12th, 2012

        I used this method to successfully restore a catalog for LR3. However, I think I read somewhere (maybe here?) that it doesn’t work for LR4 as they changed something in the database format.

      • So… If there was a method to “downgrade form a lightroom 4 to a lightroom 3 catalogue… That would work… Not sure if it’s possible though…
        i know absolutely nothing about databases, so I can just ask if you think it would be possible to adapt this method in some way to the new version 4…

      • So… In light of the catalogue dimensions requested… any ideas? Sorry to be asking so much of you, but I wouldn’t do it if it wasn’t so important… Thanks!

  4. If none of you got any idea for how to solve my issue I guess desperation is in order…
    Thanks though for your help, you’ve been kind!

  5. Thank you so very much for this. You’ve saved my last month’s edits.

    One thing I would recommend is that people trying this fix have a quick look at the SQL file. Mine ended with a “ROLLBACK TRANSACTION” command, which empties the newly-created database file!

    This worked for my LR4 database, by the way.

  6. i have the same problem with after run

    sqlite3 -init TempLRCatName.sql NewCatalogFileName.lrcat

    to create new catalog from .sql and output file is zero byte

    My solution is
    I download “010 Editor” to open my .sql dump file
    and go to the last line

    I found a SQL command “ROLLBACK; — due to errors”
    I delete that line and replace with “COMMIT TRANSACTION;”

    I try to rebuild catalog again with this command

    sqlite3 -init TempLRCatName.sql NewCatalogFileName.lrcat

    and gotcha… I have a 2.1GB catalog file return. I put it back to the original one and rename to match the original catalog

    and my LR comeback working again!!!

      • Jasmin
      • January 15th, 2013

      Dear Chairat Juengmongkolwong, THANK YOU THANK YOU SO VERY MUCH! I managed to fix my catalog thanks to your advice. I used the 010 Editor and replaced the last line like you said. It all works now. Thanks!!

    • OMFG!!!!!!!!!! Your addition just saved my life!!! Been pulling my hair out trying to work out why the restore wasn’t working and COMMIT TRANSACTION; worked perfectly!!!! THANK YOU THANK YOU THANK YOU!!!

      Worked for LR4 btw, and didn’t need to download the editor, was able to open the sql file in the standard mac text editor (just for anyone who is stuck like i was) and don’t forget the ;

      Thank you to every in this post who got me here as well, saved me hours of re-edits!!

    • omg!! lol
      I cant believe I found out! I’m in the team of LR 4…. and changing the last line worked flawless!! Thank you so much!

  7. Yes, you saved me after my RAID1 mismatched! 🙂 Thank you!

    • ReinhardG
    • July 5th, 2013

    Thanks for the writeup. Livesaver and with changing the last line it worked weill with Lightroom 5.

    • Justin
    • November 1st, 2013

    Hi, thanks so much for this help. I won’t go into the whole sob story about how important this Lightroom Catalog is to me, suffice to say that I’m desperate – being a complete novice at using terminal – can you please run me through exactly how I use sqlite3? At the moment I have the Lightroom Catalog.Lrcat and the sqlite3 files in a folder on the desktop. I’m opening the sqlite shell in terminal – then pasting in your line of code (changing the filename to match mine). All I get is three dots at the prompt (like Matteo above had). I’m not sure of what to do from here. Any help would be very welcome! Thanks.

    • Jasmin Peco
    • December 30th, 2013

    I tried everything here and enabled permissions but I have very strange problem that I can’t even copy catalog file on some other location to make a backup and when I work around terminal on the original file I get sql file being only 35mb and the catalog is alone 580mb big! 😦

    • Jasmin Peco
    • December 30th, 2013

    finder gives me notification “lightroom 4 catalog cannot be read or written osx” and it breaks reaching 125mb mark

      • rmuetze
      • December 30th, 2013

      Hi! Your hard drive is probably full or damaged. Can you check for free space left on the device you are trying to create the catalog copy on?

        • Jasmin Peco
        • December 30th, 2013

        There’s more than enough space on my machintosh hd and disk passed smart check and disk permissions are ok. Any other previous catalog copies without problem :/

    • zenzei
    • October 23rd, 2014

    I love you. You saved my catalog. A MILLION THANKS!

    • Erik Nielsen
    • November 13th, 2014

    DUDE. You are the man. I crashed my Lightroom catalog due to my laptop battery dying with the catalog open while on an attached firewire drive. I did attempt the “repair” option offered by Lightroom which did nothing. Google found this site for me. I followed the instructions above with Lightroom 5.6 under Yosemite (Mac) and it worked great. I had a backup but a couple of days old and about 600 photos behind.

    • Melissa Dunstan
    • November 29th, 2014

    I’m not having any luck. The TempLRCatName.sql folder is being created, but nothing else is happening following the directions above. is there a company that I could hire to try this for me? Or someone else on the thread who would like to log into my computer? I’m on a PC and using LR 5.7. Thank you!!

    • cplkao
    • September 19th, 2015

    Just want to say thank you very very much for your resolution, it works perfectly for me and save me lots of head and heartache.

  8. BRILLIANT!! Just had to wrap my head around the fact my catalog had a different name (so renamed it to match code); and placed sqlite3 and the catalog in my home folder (Mac: HDD/(user)). Have a project due tomorrow morning and my backup didn’t have all my revisions. Nightly back-ups from now on! Thank you!!

  9. Missing someing on SQLite, only have to files a .def file and a dll I think I need a exe ?
    Heres’ my error when I run this command:

    “sqlite3” is not recognized as an internal or external command, operable program or batch file.

      • rmuetze
      • February 12th, 2016

      Go for the tools collection at https://www.sqlite.org/2016/sqlite-tools-win32-x86-3100200.zip and try to run the command again please. Good luck!

    • I figured out, you need to download this tool.

      sqlite-tools-win32-x86-3100200.zip
      (1.28 MiB) A bundle of command-line tools for managing SQLite database files, including the command-line shell program, the sqldiff.exe program, and the sqlite3_analyzer.exe program.
      (sha1: a2562646840d39876607e0e2a56e8a372faea13d)

  10. YES! YES! YES!
    Used UltraEdit to edit the SQL file.

    • Nathan
    • August 1st, 2016

    You are my hero!! I was having difficulty getting the DB to copy into the SQL file properly with the catalog in its original location but once I copied the.lrcat file and sqllite3 into my home directory….BAM! Salvation. If I ever meet you, I am buying you a drink.

    • Marc
    • August 6th, 2016

    My god – it worked. Thank you Gerhard.

    Couple of tips on how I got it to work:
    – I extracted ALL of the sql stuff into c:\test
    – I copied my .lrcat file to c:\test
    – I opened a shell by going to start and typing cmd
    – then typed cd ..\..\ (this put me in c:\)
    – then typed cd test (this put me in c:\test\)
    – then typed echo .dump | sqlite3 lightroom_catalog.lrcat > lightroom_catalog.sql (note: no / in front of sqlite or ~/ in front of the catalogs)
    – then typed sqlite3 -init lightroom_catalog.sql lightroom_catalog_restore.lrcat (note: again, no / in front of sqlite or ~/ in front of catalog)

    Hope that helps any non-scripty people trying to get this to work!

    • Matt
    • May 1st, 2017

    i know its an old post but this just saved my bacon!! thanks !!

    • Steven
    • January 1st, 2019

    Yes, I know this is an old post, but I thought the following would be helpful as I haven’t seen it around the traps (in my searching for an answer).

    If the above doesn’t work, not all is lost. It didn’t work for me, and I tried the following, hoping it would work.

    Enter into the sqlite3 program command mode, type the following and hit the enter key:

    .open YourCorruptedLRCatalog.lrcat

    Then type the following and hit the enter key:

    .clone NewCatalog.lrcat

    To exit the command prompt (exit out of the sqlite3 application) type the following and hit enter:

    .quit

    I’m not certain what precisely happens, but my corrupted file was 1.4 GB in size and the newly created file, a clone came back as 980 MB in size. The cloning process must ignore so-called deleted data or whatever.

    Anyway, copy the newly created catalog file back to your LR folder, rename it to the original file.

    The above worked for me.

    Hope this is useful for someone.

    Steve.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Advertisements
%d bloggers like this: