Lightroom SQLite Magic

You may know that the Adobe Lightroom catalog (.lrcat) file is actually just an SQLite database. That means you can use the regular SQLite tools on it. I wanted to grab a simple list of all files in my Lightroom catalog, so I came up with this query to do the job:

SELECT root.absolutepath||folder.pathfromroot||file.idx_filename AS path
FROM aglibraryrootfolder AS root
INNER JOIN aglibraryfolder AS folder
ON folder.rootfolder=root.id_local
INNER JOIN aglibraryfile AS file
ON file.folder=folder.id_local;

Automated Shadow Copies in Windows 8.1

For some ridiculous reason, Microsoft decided to not include the Volume Shadow Copy GUI features in Windows 8.1. I wrote the powershell module below to use in a scheduled task. It creates a shadow copy on the specified volume, and optionally deletes any old shadow copies on that volume. You can actually still access the “Previous Versions” property tab for network volumes to retrieve files. To access the shadow copy files on the local machine, just use the admin UNC path (\\host\c$ or similar). To install the module, just save it as a .psm1 in a same-named subdirectory of your PSModulePath (such as c:\Program Files\WindowsPowerShell\Modules\ShadowCopy\ShadowCopy.psm1). You can then just use the Invoke-ShadowCopy command to run it.

function Invoke-ShadowCopy()
{
<#
.SYNOPSIS
    Create shadow copies for a volume and optionally delete old shadow copies
.DESCRIPTION
	Designed to be used as a scheduled task for newer versions of
	Windows that don't have built-in support for automated shadow copies
.PARAMETER Volume
    The drive letter or ID of the volume
.PARAMETER PurgeDays
	If a number of days is specified, shadow copies older than this will be deleted
.LINK
	http://www.darrellenns.com
 
.NOTES
    Author: Darrell Enns
    Date:   January 16, 2014    
#>
	param(
		[Parameter(Mandatory=$true)]
		[string]$volume,
		[float]$PurgeDays
	)
 
	$ErrorActionPreference = "Stop"
 
	$vol=Get-WmiObject -class win32_volume | Where { $_.DriveLetter -eq $volume -or $_.DeviceID -eq $volume }
	write-host "Creating shadow copy on" $vol.DriveLetter
	CreateShadowCopy($vol)
	if($PurgeDays){
		write-host "Deleting shadow copies older than $PurgeDays days"
		DeleteOldShadowCopies($vol)
	}
 
}
 
function CreateShadowCopy($vol)
{
	$sc=[WMICLASS]"root\cimv2:win32_shadowcopy"
	$result=$sc.create($vol.DeviceID, "ClientAccessible")
}
 
function DeleteOldShadowCopies($vol)
{
	Get-WmiObject -class win32_shadowcopy | Where { $_.VolumeName -eq $vol.DeviceID } | Foreach-Object `
	{
		$shadow_date=$_.ConvertToDateTime($_.InstallDate)
		$age=(Get-Date)-($shadow_date)
		if(($age.TotalDays) -gt $PurgeDays) {
			write-host Deleting old shadow copy from $shadow_date
			$_.Delete()
		}
	}
}
 
Export-ModuleMember Invoke-ShadowCopy

Here’s another approach – creating a wrapper to get the full functionality from “vssadmin”. It’s definitely a bit of a hack, but a pretty elegant one and a good read.