Renamed files after being accidentaly deleted and recovered

[boadadf]

Lightroom Version Number

LR CC

Operating System

1. Windows 10

Hi all,
Yesterday I accidentally deleted all of my wife's raw pictures from her hard disk. After the initial shock, I bought a recovery tool and I managed to restore all the files. Fe
During the process of recovering deleted files, the old file name information is usually lost. This means that the name of the restored files do no longer match the name in the LR catalog. I hope you follow me. This represents that we should now go through tens of thousands of pictures in order to re-catalog them.
I have noticed, however, that the date of the old file is kept, after the recovering, and obviously the number of bytes occupied by the file. This gives me some hope that something can still be done here to automatize the re-cataloging process. By reading the forum I learned that the lrcat file is in fact a sqlite database. I could open it and browser the tables, however I am a bit lost. I can imagine that there must be a table where the information of each file is stored.

And that is my question. Do any of you know which table contains this information? Would you happen to have a detailed schema of the lrcat database?
Hopefully the information of the creation date or the size the files is stored as well. If I could make a match between this information, old file name, size and/or date, I could create a script to find the new file and renaming it to the old file name, thus making it recognizable by LR.

Any help or comments is highly appreciated.

 

[Replytoken]

Welcome to the forum and sorry to hear about the accidental deletion. I am glad that the recovery software was successful, but yes, renaming is going to be a bit of a chore. I cannot answer the database question (but there are other members that possibly can), but I do have a question for you. Did you rename your wife's images when (or prior to) importing them into the LR catalog? If so, you could attempt to rename them again with the same scheme. There are a host of renaming programs that could be used to re-create the name outside of LR. If you were successful, and you could re-create the folder structure, then LR should be fine with it. In either event, make a copy of the rescued images and tuck them away. Always work on a copy of the images in case whatever you attempt does not work.

Good luck,

--Ken

 

[Califdan]

What a disaster. I don't have much joy for you in your renaming problem but will add an idea at the end. But, first, just to assure that we haven't missed something,
1) Please verify that you checked the recycle bin on your computer for those images. If so, and you recover the images from the recycle bin they will have their prior file names and if you recover them to their original locations LrC will just see them again.

2) Assure that you are not using Windows File History. Most people know nothing about this, but it is Windows answer to the Mac "time machine" technology, however at some point someone would have needed to turn it on. If it was on, you can recover from that source as well.

3) Windows/10 ships with a cloud backup tool that you can turn on when you install windows (or thereafter if you go looking for it). It is usually one of the "let's get you set up" screens during the windows install process. Many people turn this off, but if it were on, Windows would be making copies of your files on their Cloud.

4) Make sure you aren't using some 3rd party SW that you'd forgotten about. Something like Carbonite or CrashPlan or a slew of others.

The hair brained idea is this. Many (perhaps most or all) of the missing images still have previews in the LR catalog folder. You can create JPG's from these previews through a couple of different methods. I suspect that some or all of these methods will retain the most recent file name from the catalog. These will be low res JPG's unless you happen to have high res previews like 1:1 previews. after you get these Jpg's, tere are several products available to "find dups", some of which compare actual image content (i.e. pixels) to find the duplicates. What they do is they downgrade the images they find to a somewhat low resolution (some let you specify how low res to go) than compare those. So, a RAW would match the already low res JPG created from the previews. The tools then show you these side by side as match pairs. I'm not quite sure what the next step is, but if you can get one of these tools to output a file of matched pairs someone could write a script to rename the "recovered" file to the same name of it's "preview match" but keeping the file extension from the recovered file. It's a long shot but may be a possibility.

Good luck, and I hope it's not too uncomfortable sleeping in the garage.

Dan

 

[johnrellis]

This problem comes up on the forums a couple times a year, not often enough to motivate a plugin developer to create a solution.

At the highest level, your approach is correct -- match photo metadata stored in the catalog with that stored in the files. But you'll have to use other fields -- the catalog doesn't store the file byte size, and the file system's last-modified date can't be matched reliably with the date fields stored in the catalog .

Here's a recipe that will work. If you're not experienced with command-line scripting, I suggest you find a friend who can help you with this, sincere there is a very long list of details to get right:

1. Use the free Exiftool utility to extract from the restored photos the restored file name (e.g. "restored_57329"), FILE:FileType (e.g. "NEF"), EXIFateTimeOriginal, and the pixel dimensions into a CSV file.

2. Use the Any Filter plugin's Sort command or the List View plugin to extract a CSV from the catalog containing the full file path (e.g. /users/john/pictures/2020/2020-12-25/IMG_1234.NEF), File Type, Capture Date, and the pixel dimensions.

3. Use Excel, Google Sheet, or your favorite scripting language to join the two spreadsheets using file type, capture date, and pixel dimensions.

4. From the joined spreadsheet, generate the script that moves the restored files into their original folders, creating the folders if necessary.

Note that some troglodyte manufacturers such as Sony don't store fractional seconds for capture date, so you could have bursts all with the same file type, capture date, pixel dimensions, and exposure data. For these, you could use Exiftool to extract the manufacturer-proprietary fields that order photos in a burst (e.g. MakerNotes:SequenceFileNumber for Sonys) to disambiguate the multiple photos with the same capture date.

Also, if you had the option Catalog Settings > Automatically Write Changes Into XMP set at the time of the disaster, you could use Exiftool to extract XMPreservedFilename from the restored .xmp files and use that to do the match. This will only work if you didn't do any file renaming prior to the disaster, in which case the file name currently recorded in the catalog won't match XMPreservedFilename.

 

[johnrellis]

Here are links to the two plugins I mentioned: Any Filter and List View.

 

[Jim Wilde]

But you'll have to use other fields -- the catalog doesn't store the file byte size

John, I don't know if it helps, but you can find the file byte size in the AgLibraryFile table in the "importHash" column. The number after the filename contains the file size in bytes. I'm pretty sure it's the contents of the importHash that are used by the duplicates detection process in LrC (capture date, file name, file length).

 

[johnrellis]

Thanks. That column isn't made available to plugins, so Any File and List View can't make it available for download into CSV. I can't find it now, but I recall years ago a thread with the late Rob Cole discussing his findings that table column wasn't always reliable, that LR didn't always keep it updated. But it might be useful for @boadadf to try, since he's experienced with accessing SQL databases.

 

[Jim Wilde]

IIRC, Import from Another Catalog leaves that field blank. I did bug that years and years ago, though I doubt anything has been done about it (though haven't tested lately).