Select-DbaBackupInformation -AsGroup or new Group-DbabackupInformation

[agowa]

Summarize Functionality

It would be great if Select-DbaBackupInformation could either be extended or a new Group-DbabackupInformation could be added that links together Differential, Log and Full backups to allow going through the backup chain before a restore.

Usage like Get-DbaBackupInformation -Path $allFiles | Group-DbaBackupInformation get backups grouped. For a full backup it should just return the full backup itself as a group and for e.g. Differential Backups it should return the full backup and the differential backup.

This is necessary because just grouping based on the DatabaseName is not always a reliable way of identifying a continuous restore path. Esp. if database names are not unique (like when restoring files from a contractor or customer).

Is there a command that is similiar or close to what you are looking for?

Yes

Technical Details

Currently the only way is to do something like this is by trial and error e.g. having a for-each loop around a try catch to find which full backup belongs to which differential backup (e.g. the minimum set of input objects that Select-DbabackupInformation parses without throwing an error of [10:48:50][myScript.ps1] Fullname property not found. This could mean that a full backup could not be found or the command must be re-run with the -Continue switch.)

Alternatively something that provides the backup chains as a graph could also solve my current limitation.

[wsmelton]

Why do you need to go through the chain itself? Our Restore command we have does that for you so we would need more context on the need or solution you are trying to create with the command.

In reference to just grouping, you can do this natively in PowerShell on whatever property you like, again though not sure why it's needed.

[agowa]

Because I need the information and not want to do the restore? A graph would just be the most simple datastructure to represent these interactions. The main goal is to manage the backup files at this stage, so I do not really want to restore anything but just to parse out the information about the files presented.

[agowa]

The internal function Test-DbaLsnChain and the public one Select-DbaBackupInformation can be abused to generate this graph. Basically you need to do an O(n!) operation by testing every possible subset of the set of all backup files with it. When the chain is missing one then they'll throw an exception (if -EnableException is present at least).

And both are currently not that great of an option.

Maybe to rephrase the initial problem into two smaller ones:

1) Given a list of backup files for multiple databases, group them into backupsets for individual restores so that all files that are needed for restoring the latest version of any of these databases is within the same set, but no additional one is. [Microsoft.PowerShell.Commands.GroupInfo[]]$groups = Get-ChildItem -PSPath '\\backup01\dbBackups\' -Recurse | Group-DbaBackupInformation (return type is the same as Group-Object returns). 2) Identify the path of the relationship of the different backup files within the set,

Both help with management tasks for backup files. Like checking the latest version, or just showing their dependencies. As well as using this kind of information for other administrative tasks, like increasing quotas or deleting old backups that are no longer needed (I.E. if multiple differential backups against the same full backup are performed then only the latest differential backup and the full one are required for a restore. And one may want to write a script that does take care of the lifecycle of these backups (esp. when they're at a secondary offsite storage and no longer "just on the disk" of the sql server that created them.