Probably a bit niche this one, but as I scratched my head trying to work it out I’ll share it to save others in future.

The Problem

Working on some new functionality for DbaTools, adding the ability to cope with striped SQL Server backupsets in fact. Everything’s going pretty swimmingly, I’ve got a process for finding the backup files and grouping them together.

Adding them to the Restore device collection is nice and easy as I have a Object to loop through:

Foreach ($RestoreFile in $RestoreFiles)
{
    $Device = New-Object -TypeName Microsoft.SqlServer.Management.Smo.BackupDeviceItem
    $Device.Name = $RestoreFile.BackupPath
    $Device.devicetype = "File"
    $Restore.Devices.Add($device)
}

nice and simple.

The problem comes when I want to reuse my restore object as I loop through the Differentials and the transaction logs. If these Devices aren’t removed, then SQL Server tries to reuse them on every run. As this code is designed to be run anywhere by anyone I’ve know idea of the number of devices needed. Not only will people stripe their backups across differing numbers of files, but it’s possible to strip your backups differently for each type! So this is perfectly fine:

  • Full Backup – Across 4 files
  • Differential Backup – Across 2 files
  • Transaction Backup – Across 1 file (you CAN strip transaction backups if you want)

and you can even do it differently for different runs.
I’ve found examples for c# if you know the devices names that don’t work in PowerShell. I’ve found methods using Get-Member that don’t appear to be documented, and generally worn a dent in my work desk with my forehead (annoying my co-workers with the dull thud).

The classic PowerShell way of using ForEach fails:

ForEach ($Device in $Restore.Devices)
{
    $Restore.Devices.Remove($Device)
}

Fails with the nice red error message:

An error occurred while enumerating through a collection: Collection was modified; 
enumeration operation may not execute..

The Solution

The oft forgotten while loop!

while ($Restore.Devices.count -gt 0)
{
    $device = $restore.devices[0]
    $restore.devices.remove($Device)
}

No need to know beforehand how many objects you need to remove, though here I’m expecting at least 1. If there was a chance of 0 runs I’d use a do…while loop instead. But as if there wasn’t even 1 backup device to remove I’d have had an error during the restore I’m fairly safe here.