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!
The world according to Gerhard 
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!
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!
Worked as a charm! Thanks a lot!
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!
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?
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
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!
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…
Nothing 😦
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
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!
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!
Hi Matteo,
I’m sorry I can’t provide youwith a solution to your problem. There is a guy at Adobe who is being recognized as the Lightroom catalog guru. See this post for his name and contact details: http://www.lightroomforums.net/showthread.php?15926-Repair-of-corrupted-catalogs It may be worth a try. Good luck! Best regards
Ruediger
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.
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!!!
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!
Hello, thanks for the informations! But i am not ggod at all with SQLITE…I have a corrupt LRCAT. Can you help me? Many Many thanks!!
Yes, you saved me after my RAID1 mismatched! 🙂 Thank you!
Thanks for the writeup. Livesaver and with changing the last line it worked weill with Lightroom 5.
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.
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! 😦
finder gives me notification “lightroom 4 catalog cannot be read or written osx” and it breaks reaching 125mb mark
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?
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
I love you. You saved my catalog. A MILLION THANKS!
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.
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!!
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.
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!!
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.
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)
YES! YES! YES!
Used UltraEdit to edit the SQL file.
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.
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!
i know its an old post but this just saved my bacon!! thanks !!
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.
Thanks much — saved a tremendous amount of tedious edits I made over the last couple days between backups. Thanks also to Chairat for the hack that made everything finally work. Cheers!