Adobe Photoshop Lightroom

Loving Your Lightroom Catalog

LR_databaseI’ve mentioned this topic in several previous posts, now I’m devoting this entire article to the critical issue of Lightroom database backups and, to a larger extent, the nature of the .lrcat and databases in general. (Admittedly, this isn’t sexy stuff, but you need to treat your Lightroom Catalogs with loving care!) This article will help you understand the Lightroom Catalog and to develop healthy workflow habits for maintaining the Catalog in optimum condition.

Note: in official Lightroom-speak, the database is referred to as the Catalog. In this article I will use the two terms interchangeably… Catalog = database.

Data at the Core
The database/Catalog is one of Lightroom’s great strengths. Not only does it make finding and organizing things easier, it’s what allows the wonderful flexibility of non-destructive editing using metadata. The unlimited History maintained by Lightroom is one example; another is Virtual Copies – also one of Lightroom’s greatest features – made possible by the database.

Lightroom Catalog databases are contained in what appears as a single file on the hard disk. If you don’t know the location of your main Lightroom catalog, take the time to find it. If you are using the default name, it’s called Lightroom Catalog.lrcat. You can use more than one Lightroom database, but only one can be open at a time.

If you use Lightroom for your photo editing, you need to protect this database file at all costs!! And understand that keeping it clean is only half the battle: maintaining at least two recent backups is an absolutely essential workflow practice.

The Lightroom Catalog uses a database engine called SQLite, based on the industry standard MySQL. Your operating system (Mac or Windows) works with Lightroom to handle the data transfers in the background. So it’s natural that we’re not often aware of everything going on in the database. But it’s easy to see how occasional problems can arise within a database containing hundreds of tables and potentially hundreds of thousands of individual records.

Bad Data!
Corruption in the database can occur at any time. In my work training clients in Lightroom since it was released, I have seen a half dozen cases of corruption in the Lightroom database. In some cases, the client had good backups; only minor loss and nuisance. Other cases were complete losses requiring a new database to be created. Imagine rebuilding your image library and redoing all the editing you’ve done for the past year. This can be avoided; I’ll tell you how in a moment.

First, we need to understand a few basic facts about computer databases. A typical database contains multiple tables, which in turn are made up of records. A Lightroom Catalog file for version 1.4.1 has nearly 200 tables, each containing a specific kind of data. There are tables that contain your image edits, others that hold information about your workspace and preferences, and other tables for keywords, etc. Each record in the database is an entry in the table and contains the actual data, such as the value of a specific slider or the location of a file on the hard disk.

*Note that the images themselves are not contained in the database; only references to the pixel data in the actual image file.

There are signs that appear when there’s trouble somewhere in the database. Lightroom running slowly and becoming unresponsive, a Missing Files collection in the Library panel, or files that mysteriously disappear are all indications of data corruption somewhere in the database.

(Interestingly, one of the more common database problems I’ve seen makes itself known with an error when switching from Develop to Library. As this demonstrates the database really has a huge effect on all aspects of working within Lightroom.)

So what’s a photographer to do?
Take care of your database. Prune it. Clean it up. Maintain it. Nurture it. And Back Up Obsessively.

Here are my top tips for taking good care of your Lightroom Catalogs (and protecting yourself from complete disaster when something goes wrong, as it inevitably will).

1. In the Library, keep an eye out for these signs of broken links and possibly corrupt files/bad data and correct the problems as soon as possible:

a. Folder List – Folder Names in Red are no longer accessible by Lightroom and the files cannot be edited. Update the folder locations or remove them.

b. Library Panel – the Missing Files collection indicates malformed records for file locations in Lightroom’s database. If possible, remove and re-import these files.

**IMPORTANT – Whenever you’re removing files and intend to bring them back in, you need to keep all the changes made in Lightroom, including Stacks, Virtual Copies and editing data. Export to Catalog… the Missing Files before removing them. Then do Import from Catalog… and everything will be ship-shape.

c. Grid – thumbnails for files that have question marks in their upper right corner cannot be located by Lightroom. Find and relink them or remove them from the catalog.

2. The equivalent of every 15 full work days or so, run the Relaunch and Optimize command (under Catalog Settings).

3. Back up your Lightroom catalog/database files every work day!!

Option A. Set the preferences for Lightroom to back up automatically. (LR must be launched for this event to occur)

Option B. Manually back up your database using drag and drop or backup software

I do both. The Lightroom automated backup is especially good because it optionally also tests the database for problems. Even if you don’t complete the backup, this Test is a good thing to let run frequently.

Keep a minimum of 2 recent backups. Be careful about backups when you’re having problems; there’s not much point in backing up a corrupt database. Make backups at critical points where things are OK and you’re about to do a bunch more work.

Some days I might make 10 backups of my Lightroom database. These are days where I am making major changes and I want to have recent backup points just in case.

Also, I make sure that the changes I make to the files are saved into the files constantly. Losing all my Lightroom database capability is a minor worry compared to that of maintaining current versions of my files and not having to redo my work.

There is no doubt that effectively managing your Lightroom catalog is time consuming and sometimes frustrating. We all know computers can be unpredictable and that problems will arise. The key to overcoming obstacles is to have a good system in place and maintain it religiously. If you start today, work on it regularly and maintain good work habits, it’s possible to achieve and maintain a very reliable Lightroom database.

If you want to see the inner working of the .lrcat, open it with a SQLite browser. You can also use the browser app to optimize the database. Just be careful…

15 Comments

  1. I would like to use my lightroom database with its keywords to do a mysql database for an internet archive. You know if it is possible?

    Reply
  2. Yes, you can definitely do this.

    If all you need is keywords, you can export them from within Lightroom (to a text file) and import that data into your MySQL database.

    If you want to move multiple tables and data from Lightroom to your MySQL database, you can do this with database browser software (though I’m not sure what program would work with both SQLite and MySQL).

    You may need to export from SQLite to an intermediate format, then import to MySQL.

    Since this is all open-standard, everything should migrate with no problems.

    The only challenges will be making sure table relationships and structured queries are handled correctly (if necessary), and keeping the two databases in sync.

    Please let us know how it works out. Good luck!

    Reply
  3. SQLlite has libraries for most web programming languages (php/java/python/perl/ruby), so you can open directly lightroom catalogs from that languages and perform queries on it.

    So, if you, like me, backup you db/photos on a web hosting then you just need to write some code server side and you will have your keywords uptodate any time you backup your catalog.

    Hope this helps.

    Reply
  4. Just for clarification:

    sqlite is _not_based on mysql, as stated in the paragraph “Data at the Core”.

    Then again, you can use the standard program sqlite3 to dump database tables and even export it to csv, html, etc.

    The problem with the databas is to connect the many tables together, so you can select the right informations you want.

    I have searched for an exact description of the db format, but haven’t found one yet.

    By the way: Backup of the Lightroom catalog is so much easier on a Mac with Leopard and the built in Time Machine, you just need a good external hard drive and that’s it.

    Reply
  5. Daniel – you’re right, thanks for pointing that out. I should have said that SQLite is based on industry standard SQL. MySQL and SQLite are both “flavors” of SQL.

    Reply
  6. I have several catalogs in different drives. (Before I knew how to use the program correctly!)
    I want to merge all of them into one catalog but some of them have the same name. (i.e.
    Lightroom Catalog.lrcat) How do I export these catalogs so they will merge with the others with same name? Thanks.

    Reply
  7. Jim, here’s the best way to approach this:

    1. Make a new catalog with a unique name (so you can tell it apart from the others). This is under File > New Catalog…
    2. From within this new catalog, use the “Import from Catalog” option (also under the File menu).
    3. Do an Import from Catalog for each of your other separate catalogs. When you’re done, you will have everything from those catalogs in all the new one.

    When doing these Import from Catalog operations you have options for how the actual image files (and potential duplicates) are handled. Depending on your file/folder structure you may need to enable some of the options. However, if you’re OK with where all the actual files are, just leave all the settings at their default.

    Let me know if you run into any trouble. Good luck!

    Reply
  8. When I launched Lightroom today I was asked a question (that I didn’t pay close attention to, could kick myself now) which asked something about the catalog. I only use one catalog so I didn’t think much of it. When Lightroom opened though is was in the state of use from about a month prior, no current photo/imports are present. I import photos almost daily. I know they’re still in here somewhere, but how do I get the current, up to date state of my catatlog back?
    Thanks you
    Shiree

    Reply
  9. Shiree-
    It sounds like you have multiple versions of your catalog on your hard drive (maybe backups?) and that you simply opened the wrong one. Not to worry.

    Before I explain the solution, I need to re-emphasize the importance of always knowing what catalog you’re working in. Pop-up messages and windows that open when you’re launching Lightroom really need to be carefully heeded to avoid problems. If you get in the habit of ignoring Lightroom’s messages at startup, you’re headed for trouble.

    Managing catalogs in Lightroom is really as simple as opening “files” like you would in any other program. A catalog is simply a Lightroom “file”. You can only have one catalog open at a time, and you always need to know what catalog you’re working from.

    With Lightroom open and running, you can go to the Catalog Settings box (Mac under Lightroom menu, Windows under Edit menu). This will tell you what catalog is open, and gives you a button to show that catalog on your desktop. This is an essential aspect of managing multiple catalogs; use it frequently!

    Also, Lightroom has a Preferences setting that determines what catalog to open when launching the program. In Preferences > General > Default Catalog you have options to “Load Most Recent Catalog”, “Prompt me”, plus a list of recently opened catalogs and “Other…” which lets you choose one a catalog from outside the provided list.

    When you’re in Lightroom (in any catalog) you can use the File > Open Catalog to open a different one. This will quit Lightroom and restart usign the selected catalog.

    OK, that business aside – you need to find the catalog that you normally work in, which I call the “master” catalog. We should all have one main catalog that contains our most recent photos, the work we’ve done on them, etc. Other catalogs normally are used for temporary purposes, specific to a given situation.

    So, understanding that you are in the wrong catalog, it’s probably easiest to use the “Open Catalog” command and navigate to your master catalog and open it. If you’re not sure where it’s located, try the “Open Recent” command instead.

    With your master catalog open, check your Preferences to see what catalog is set to open by default. It’s probably set to “Load most recent Catalog” which, if you only work from one catalog, is usually OK. But you can change this to always open one specific catalog, regardless of what is the “most recent”.

    I need to also point out that the issue of choosing what catalog to work in is especially important when you’re upgrading to a newer version of Lightroom. Each version of Lightroom requires a catalog upgrade, which means that you will generate a new catalog from the older version. Going forward, this is the catalog to work in. If you know you’ve already upgraded your catalog to the current version, and you get the message that the catalog needs to be updated, STOP! Lightroom is looking at the wrong catalog.

    **I had a client who was unknowingly working from as many as eight (8!) different catalogs. Obviously, this will present “challenges”. 😉

    So, to restate my original point, always know what catalog is open, and what catalogs are saved on your hard drive. Be careful that you don’t work in multiple catalogs un-intentionally.

    You can search your hard drive for files ending in “.lrcat” to identify all the catalogs on your system and decide what to do with them.

    Please let me know if you have any other questions, or trouble with the steps presented.

    Thanks for your comments!

    Reply
  10. Hi there. I have backed up my image files to an external hard drive as I wanted to clear space on the computers hard drive. I am having trouble redirecting the catalog to the new file location.
    I have started a new catalog that uses my most recent uploads in the new location but was not sure how to make the older catalog use the images in the new location…

    John

    Reply
  11. @John Skuja
    John – the easiest way to point your old catalog to the new location is to update the links to all the top-level folders in your folder list. When you look in the Folders panel, you’ll see question marks next to the folder names. Right click or control click on the folder and choose “Locate folder”. Then navigate to the folder in its new location on the drive and click OK to relink.

    This illustrates why it’s usually best to have all of your sub-folders contained within one top level folder. This way, if you move your image files, all you need to do is relink the top level folder and Lightroom will find all the files in the subfoders underneath.

    Please let me know if you have any questions about this. Thanks for reading and commenting!

    Reply
  12. “In my work training clients in Lightroom since it was released, I have seen a half dozen cases of corruption in the Lightroom database. In some cases, the client had good backups; only minor loss and nuisance. Other cases were complete losses requiring a new database to be created. Imagine rebuilding your image library and redoing all the editing you’ve done for the past year. This can be avoided; I’ll tell you how in a moment.”
    How much is realistic?

    Reply
  13. Nat,

    Thanks for all the good tips and comments. I am trying to organize some images and probably think too much like file manager rather than catalog function. All I am trying to do is reorganize a badly maintained (I take all the blame) file directory and at the same time keep Lightroom and Photoshop Elements up-to-date. The two products do not play well together, in my opinion. Images seem to get lost in one or the other or both and then I have to go hunting. Extremely tedious. But the real question is how can I store a collection that has a specific purpose such as an upcoming show that I want to prepare for, and not lose all the previous work or links? My file manager head says to start a new folder but when I start moving images they are now lost in space. Any ideas on how to proceed? I love the functionality of the Lightroom EXCEPT for the file management system which, to me, seems almost arcane.
    Thank-you,
    Mark

    Reply
  14. Mark, thanks for reading and commenting. Sorry to hear about your frustration.

    Here’s the deal: both Lightroom and Elements use a database to keep track of files. They use different databases. With both programs, you have to add files into the database (or “catalog”) for them to be seen by the application.

    Because they use databases, it’s possible to have one set of files, or one system of organization, on your physical hard drive, and another within the catalog. OK so far?

    If you work on a photo in Lightroom, or export newly created files, Elements may not see the changes, and vice versa.

    The best way to ensure continuity between the programs is to 1) make sure all your current files have been imported both places, and 2) going forward, use the Edit In… command to go from LR and PSE and back again. When you use Edit In…, LR keeps track of your work during that session, and any new files created will automatically be added back into the LR catalog.

    You can use the Synchronize Folder command in Lightroom to make sure everything in a folder on your hard drive is included in the catalog. Synchronize Folder also removes records from the catalog for photos that have been removed from the hard drive.

    The file system in Lightroom (and PSE) is designed to precisely reflect your file structures on the hard drive. So keeping files organized properly on the hard drive is essential.

    You can use Lightroom Collections to create alternative groupings for photos, without requiring moving or saving any new files on the physical hard drive.

    I hope this helps. I offer private, one-on-one consulting via remote screen sharing software Contact me directly if you’d like to discuss further. Thanks again!

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Get Photography tips, techniques and
tutorials FREE to your inbox

We value your privacy and will never share your details with anyone.

Almost there... Check your inbox to confirm.

Pin It on Pinterest

Share This