SQL Powershell: Remove-SqlAvailabilityDatabase

Question

I'm writing a Powershell script to Backup and Restore a Database in an High availability environment (everything needs to be done with SMO). That includes a couple of steps (more or less: remove Database from the the Availability Group on the primary server, drop the database on the secondary server, create backups, restore backups, add database again).

The script was running fine, while executed on one of the database servers. But now i have to run it on a Management/Application Server. That gives me now a couple of new Problems...

Connection to the server works fine. Then i read all the databases from the availability group:

$Global:ProdKopieDatabases = $server.AvailabilityGroups[$Global:ProdKopieAvailabailityGroup].AvailabilityDatabases

If i check the $ProdKopieDatabases it shows me exactly the count of databases i expect. It's als an object of the type Microsoft.SqlServer.Management.Smo.AvailabilityDatabase

Further down the script i need to remove the database from the availability group

foreach($DatabaseToRemove in $Global:ProdKopieDatabases)
    {
        try
        {
            Remove-SqlAvailabilityDatabase -InputObject $Global:ProdKopieDatabases[$DatabaseToRemove.Name.ToString()] -ErrorAction Stop
            #Remove-SqlAvailabilityDatabase -Path $($Private:MyAgPrimaryPath + "\AvailabilityDatabases\" + $DatabaseToRemove.Name.ToString()) -ErrorAction Stop
            Start-Sleep -Seconds 5
            Write-Host .. OK.
        }
        catch
        {
            Log-Write ($Error[0].ToString())
            Return 1
        }
    }

If i check the $Global:ProdKopieDatabases variable before the first foreach, i see all the databases (ie 3). But after the Remove-SqlAvailabilityDatabase the database that has been removed from the group, has also been removed from the $Global:ProdKopieDatabases variable.. Therefore, when the foreach comes to the second round, i get an error:

Error: Collection was modified; enumeration operation may not execute.

I dont understand why? It's as if the variable is "by reference" connected to the availability database.. If that is the reason, how can i change that?

This even happens if i try to add it to another variable and work with $c only:

$c = New-Object Microsoft.SqlServer.Management.Smo.AvailabilityDatabase #$Global:ProdKopieDatabases
$c = $Global:ProdKopieDatabases | Select *

While i was running the script directly on the SQL Server, i was able to do it the following way

$Private:MyAgPrimaryPath = "SQLSERVER:\SQL\$PrimaryServer\DEFAULT\AvailabilityGroups\$Global:ProdKopieAvailabailityGroup"

$Global:ProdKopieDatabases = dir $Private:MyAgPrimaryPath\AvailabilityDatabases

The foreach was the same, but the remove was different:

Remove-SqlAvailabilityDatabase -Path $($Private:MyAgPrimaryPath + "\AvailabilityDatabases\" + $DatabaseToRemove.Name.ToString()) -ErrorAction Stop

After this Remove-SqlAvailabilityDatabase command the variable $Global:ProdKopieDatabases still had all the databases in it...

I have absolutely no idea anymore. I understand the error message, but i do not understand why the variable also gets changed..

Any help is highly appreciated!

Thanks in advance

Answer 1

I've found a solution/workaround... (it's ugly)

First i fill the variable:

$Global:ProdKopieDatabases = $server.AvailabilityGroups[$Global:ProdKopieAvailabailityGroup].AvailabilityDatabases

Then before the foreach, i create a new variable with just the names in it:

$c = $Global:ProdKopieDatabases | Select Name

I let the foreach run over the newly created variable and for the -InputObject i determine the object every time new again...

foreach($DatabaseToRemove in $c)
    {
        try
        {
            Remove-SqlAvailabilityDatabase -InputObject $server.AvailabilityGroups[$Global:ProdKopieAvailabailityGroup].AvailabilityDatabases[$DatabaseToRemove.Name.ToString()] -ErrorAction Stop

            Start-Sleep -Seconds 2
        }
        catch
        {                
            $Error[0] | Format-List -Force
            Return 1
        }
    }

Further down the script i only work with the $c variable and not with the $Global: anymore...

Maybe one day i'll understand why my $Global: variable was behaving like that.. Until now, i have no idea...

Hope this post can help some one some day ;-)