Creative ways to find a database backup within multiple restorepoints?

[csmall]

When using a SQL Availability Group where one node has an active copy of the database at any given time and the other node does not have a readable copy of the database, what are some creative ways to use Powershell to determine which restorepoint actually contains the database for a restore for an automated restore script.

So you have $RestorepointNode1 and $RestorepointNode2 that each contain their respective latest restorepoint. I guess you could use a try/catch?

Code: Select all

$RestorepointNode1 = Get-VBRApplicationRestorePoint -SQL -Name "SQLSERVERNODE1" |Sort-Object creationtime -Descending |Select-Object -First 1
$RestorepointNode2 = Get-VBRApplicationRestorePoint -SQL -Name "SQLSERVERNODE2" |Sort-Object creationtime -Descending |Select-Object -First 1

$Restorepoints = $RestorepointNode1,$RestorepointNode2

foreach($Restorepoint in $RestorePoints) {

    try
    {
        $Database = Get-VBRSQLDatabase -ApplicationRestorePoint $restorepoint -Name "DATABASENAME" -ErrorAction Stop
    } 

    Catch
    {
        $Database = Get-VBRSQLDatabase -ApplicationRestorePoint $restorepoint -Name "DATABASENAME" -ErrorAction Stop
    }

}

Then $Database would contain whatever one didn't have an error..

How would you do it?

[csmall]

This actually does not work as I thought it might. It depends on the order of nodes in $Restorepoints. If I reverse the order of nodes it doesn't do what I wanted.

[csmall]

I'd prefer an if statement but I'm not sure if I can use one yet.

[csmall]

Looks like I got it to work with an if statement after all.

I think I'm good from here but I would love to see how others would handle it.

I see now that the try/catch way of doing it didn't really make any sense