Background

Lightroom uses a centralized catalog file to store not only all of the image metadata but also the non-destructive raw develop recipes. As such, the catalog is a centrally important file for Lightroom users. Though the loss of the catalog file won’t cause your original raw files to be lost, it will destroy the “recipes” that Lightroom uses to transform those raw images into a final developed output. As such, Adobe built a mechanism into Lightroom to create backup copies of the catalog file so that users had a mechanism to recover their work if the catalog somehow became corrupted.

Early incarnations of Lightroom, right up to early versions of Lightroom 6/CC 2015, backed up the catalog by simply copying it to subfolder in the same path as the catalog. Many users, especially those astute to have noticed that the catalog is nothing more than an SQLite database file, complained that these backups were inefficient and took up wasted space.

I’ve previously written about some of the major inefficacies in how the catalog stores data, and how it stores data; especially history data. Because of these inefficacies, it’s reasonably easy for the catalog to become quite large.

The redundant storage of string information in the catalog means that it artificially inflates much faster and larger than it should, mostly due to the storage of history states. Secondly, it means that the catalog can be compressed to high compression ratios because of the level of redundancy in the data.

For example, my catalog file is 1.18 GB (1,273,036,800 bytes). Compressing it into a basic zip archive drops the size to 118 MB (123,769,009 bytes), or less than 10% of the original size. More sophisticated compression algorithms such as the LZMA algorithm used by 7-Zip archives, can compress the same catalog down to 44.4 MB (46,658, 445 bytes), or a mere 3.6% of the original catalog file size.

Though Lightroom was never designed operate from a compressed catalog file, there was no reason that the backup process couldn’t compress the catalog into an archival container, such as a zip file. This would have the advantage of dramatically reducing the size of the catalog backups.

Somewhere in the Lightroom 6/CC 2015 development process, Adobe decided to implement a compression system for the catalog backups. The chose the zip format for the backups, which is nearly universally retable on PCs, Macs, and Linux based systems.

The Zip archive format has some limitations, arguably reasonable ones at that. One of those is that the zip archive itself cannot exceed 4GB in size. That said, given the highly compressible nature of the Lightroom catalog, to exceed that 4GB limit one should need a catalog on the order of 40 GB in size.

This would be all good, except somewhere in the development process Adobe apparently decided not to compress the catalog at all. They still stick it in a zip archive file, but they apply no actual compression to it. As a result of this, Adobe’s zipped up catalogs end up being as big as the native uncompressed catalog file they’re backing up.

The problem shouldn’t be hard to see. With no compression being applied, if you catalog exceeds 4 GB, it reaches the limits of the zip archive format and cannot be saved. Lightroom completely unhelpfully informs you of this with an error that implies you’re out of disk space, and prompts you to save the catalog backup to somewhere else: which obviously doesn’t work.

Effectively this bug, renders the Lightroom 6/CC 2015 catalog backup system useless to those with large catalogs.

Moreover, due to Adobe’s ineptness there’s no simple workaround for this inside Lightroom. You cannot disable the compression in the backup, so you can’t just have Lightroom make a simple copy of the database.

This has a knock on effect, in that Lightroom automatically optimizes your catalog file (as far as I can tell it’s just running the SQLite vacuum command) after every successful backup. This optimization, though not normally something that generates much savings or performance gains, is SQLite’s way to insure that data is packed in the optimal format in the file structure and free up space from deleted entries.

Of course, you can manually run the catalog optimization function in Lightroom, but not having it done automatically means having to remember to do it.

Workaround Version 1

I’m calling this a first attempt at a workaround, as I’ve thrown this together based on an old Power Shell script I had to handle Lightroom backups before Adobe implemented compression. That said, I consider this an expedient solution not a good one. Ideally Adobe would have fixed Lightroom’s broken zip implementation and we wouldn’t have to deal with this at all.

I’m posting this in case there is someone else who’s reasonable technical enough in a similar situation. There are obviously a number of limitations in this solution:

• It only runs on Windows, and a version that has PowerShell installed
• It has some options that have to be configured manually

ASIDE FROM A BEST EFFORT TO TRY AND ANSWER QUESTIONS IN THE COMMETNS THIS IS PROVIDED WITHOUT SUPPORT OR HELP.

Prerequisites

I’ve written this script to support compressing the catalog with either Window’s built in zip compression or using the stand alone program 7-Zip. Functionally both options compress the catalog significantly, though 7-Zip does get significantly better compression than the built in zip archiver.

For my 1.18GB catalog I see the following compressed sizes.

I would recommend that if you have a large catalog and want to keep lots of backups, then using 7-Zip would net some appreciable storage savings. With my catalog I can store almost 3 times as many 7-Zipped backups as I can store zipped ones. If you wan to use 7-Zip, you can download it from the above link, and install it.

The second prerequisite is SQLite’s utilities. This is what preforms the “optimization” on the catalog by running SQLite’s vacuum command (again, as far as I can tell this the same thing that Lightroom does when it’s optimizing the catalog). The SQLite tools are distributed from SQLite’s site, on the download page.

You want to download the file “sqlite-tools-win-32-x86-#########.zip” under the section titled “Precompiled Binaries for Windows”.

The SQLite tools are distributed as a zip archive. To use them with this script, extract the file sqlite3.exe from the archive and place it in the same folder as the backup script.

Script

################################################################################
#
# Lightroom Catalog Backup Script
#
################################################################################ 
# 
# This backups, compresses, and optimizes your Lightroom catalog independent of 
# Lightroom.
# 
# THIS SCRIPT IS PROVIDED AS IS AND WITHOUT WARRANT OF ANY KIND. 
# FURTHER NO SUPPORT WILL BE PROVIDED FOR THIS SCRIPT. 
# THIS SCRIPT WAS DOWNLOADED FROM: HTTPS://WWW.POINTSINFOCUS.COM/
# 
# Only a set number of backups are kept, backups beyond the number to be kept 
# are automatically deleted.
# 
################################################################################
## Configuration Options
# Set the following number to the number of backups you want to keep. Set to 0
# to keep all.
$backups_to_keep = 4


################################################################################
## DO NOT EDIT BEYOND THIS POINT UNLESS YOU KNOW WHAT YOU"RE DOING
################################################################################
$myPath = Split-Path ( Get-Variable MyInvocation).value.mycommand.path
$backupPath = "$myPath\Backups"
$lrCatalogs = Get-ChildItem $myPath -Filter "*.lrcat" | ForEach-Object {
    if( $_.Attributes -band [IO.FileAttributes]::ReparsePoint ) { 
        $_ = $_.Target 
    } 
    else { 
        $_ = $_.FullName
    }
    $_
}

# End process and exit if there is a lock file in the catalog path.
if( test-path ([string]::Concat($myPath, '\', '*.lock' ) ) ) {
    exit 0
}

[bool]   $zipped      = $FALSE
[string] $7ZipExe     = 'C:\Program Files\7-Zip\7z.exe'
[string] $SQLiteExe   = "$myPath\sqlite3.exe"
[bool]   $7z          = Test-Path $7ZipExe
[bool]   $sqlite      = Test-Path $SQLiteExe 
[string] $archiveName = [string]::Concat( $backupPath, '\', (Get-Date -Format "yyyy-MM-dd HHmm"), $( if($7z) {'.7z'} else {'.zip'} ) )

function create-7zip($aDirectory, [String] $aZipfile) {
    #[string]$7ZipExe = "C:\Program Files\7-Zip\7z.exe";
    $targets = $aDirectory | ForEach-Object { $_ = [string]::Concat( '"', $_, '"' ); $_ }
    [Array]$arguments = "a", "-t7z", "-r", '-slp', '-snl', "`"$aZipfile`"" + $targets;
    &$7ZipExe $arguments;
}

function sqlite-exec( [array]$files, [string]$command) { 
    if( $sqlite ) { 
        foreach( $f in $files ) { 
            [array]$arguments = "$f", $command
            &$SQLiteExe $arguments
        }
    }
    else {
        echo "SQLite not found, Skipping"
    }
}

echo "Checking integrity of: $lrCatalogs"
$t = sqlite-exec ($lrCatalogs) "PRAGMA integrity_check;"
if( $sqlite -and $t -ne "ok" ) { 
    echo "Catalog has errors:"
    echo $t
    echo "Backup halting."
    exit 1
}

echo "Backing up $lrCatalogs to $archiveName"
if( $7z ) {
    create-7zip ($lrCatalogs) ($archiveName)
} 
else { 
    Compress-Archive -Path $lrCatalogs -CompressionLevel Optimal -DestinationPath $archiveName
}

# Clean Up Archives
if( $backups_to_keep -ne 0 ) { 
        Get-ChildItem -Path "$backupPath\*" -Include *.7z, *.zip | 
            Sort-Object Name -Descending | 
            select -skip $backups_to_keep | 
            %{ Remove-Item $_.fullname -Force }
}

# Optimize Catalog Files
echo "Optimizing Lightroom Catalog."
sqlite-exec $lrCatalogs "VACUUM;"

exit 0

Installation

Copy the above text into a blank text file (in something like Notepad). Save it with the extension .ps1 in the folder where you’re Lightroom catalog is.

Configuration

The only configuration option is backups_to_keep. This controls the maximum number of backups that the script will keep in the backups folder. The default is 4. If set to 0, the script won’t delete any old backups.

Running

The easiest way to run the script is to right click on it and choose run in PowerShell.

Alternatively you can create a shortcut with the target set to C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command "& 'c:\full\path\to\the\script\lr_backup_scrpt.ps1'", and the start in line set to the same folder as the script.

Support

THERE IS NONE.

I’m providing this as a courtesy to anybody who might find this useful. However, I’m not in the IT/computer support business and I’m not interested in getting into that. I want it to be abundantly clear that I will not be providing support for this script.

Moreover, I will not be held responsible if you don’t know how to use this and it somehow deletes your computer, your photos, or your Lightroom catalog, especially if you’ve modified it in any way.

That said, if you have a question or problem, feel free to post a comment and if I can answer it I will try (or maybe we’ll get lucky and someone else can answer it).