RumSoft Blog

RumSoft Technology

Lightroom – moving files and folders by manipulating the database file

Comments Off

I use Lightroom for my photo collection. The photos reside on a network drive which is getting a little full so I need to move my photos to a new location.

I know Lightroom can move the photo collection but for various reasons the files and folders had already been moved to a new location outside of Lightroom so I needed a way to tell Lightroom that the files and structure had moved.

I knew that Lightroom keeps all the file information in an sqllite database and using Firefox I could access it.

The instructions here were completed on a Lightroom 2.5 data file. I have not checked this with any other version.

Before we start. If you follow this advice I recommend you backup your Lightroom data file in case this goes wrong.

I downloaded SQLite Manager for Firefox available from https://addons.mozilla.org/en-US/firefox/addon/5817

Once installed run SQLite Manager (Tools menu then SQLite Manager)

You should see a screen like this:

SQlite manager running

SQlite manager running

The SQLite manager will open a screen like the one to the left.

This is a superb graphical display to help you manipulate a database file.

Use the Open Folder icon to navigate to the Lightroom data file. The tooltip will show you that this icon is called “Connect Database” as you can see from the screen shot.

We now need to open the data file.

The find file dialog will appear and you need to navigate to the folder containing your Lightroom file.

Remember to change the file type from SQLite DB Files to All Files otherwise the correct datafile cannot be found.

Open lightroom database

Open lightroom database

 

 

 

 

 

 

 

 

 

 

 

Selecting a lightroom data file

Selecting a lightroom data file

 

 

 

 

 

 

 

 

 

 

 

 

Lightroom data files have the extension .lrcat

Select the data file.

After you have opened the file SQLite should now look like this:

 

 

Lightroom database view

Lightroom database view

 

 

 

 

 

 

 

 

 

 

 

 

 

The name and location of the open file will be shown at the top of the screen.

The left hand panel lists the tables within the database.

The right hand panel is where we can look at the structure of the data file and execute SQL against the database.

The tables we are interested in are AgLibraryFile, AgLibraryFolder and AgLibraryRootFolder

In this example the photos are stored on Drive “S:\Photo Libraries” but have been moved to “D:\Documents”.

To update the database file follow these steps:

  1. Click on the “Execute SQL” tab
  2. Type the following SQL statement: UPDATE AgLibraryFile SET robustRepresentation=replace(robustRepresentation, ‘S:\’, ‘D:\Documents\’) WHERE AgLibraryFile.robustRepresentation like “%S:\Photo Libraries\%”
  3. Click Run SQL
  4. Replace the previous statement with: UPDATE AgLibraryFolder SET robustRepresentation=replace(robustRepresentation, ‘S:\’, ‘D:\Documents\’) WHERE AgLibraryFolder.robustRepresentation like “%S:\Photo Libraries\%”
  5. Again click the run button
  6. Replace the statement again with: UPDATE AgLibraryRootFolder SET absolutePath=replace(absolutePath, ‘S:\’, ‘D:\Documents\’) WHERE AgLibraryRootFolder.absolutePath like “%S:/Photo Libraries/%”
  7. Finally click the run SQL button

The Lightroom database should now have been updated to reflect the correct location of the photo files and none of them should be orphaned.

  • Blogroll