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

I was able to narrow the problem down to the way how i fill the variable $Global:ProdKopieDatabases . If i fill it with the dir $Private:MyAgPrimaryPath\AvailabilityDatabases the Remove-SqlAvailabilityDatabase -InputObject works perfectly fine... If i fill it with the $server.AvailabilityGroups[$Global:ProdKopieAvailabailityGroup].AvailabilityDatabases it behaves different... Any ideas? What can i check? Is there something like byValue instead of byReference? — Dan Stef
– Dan Stef, Commented Oct 15, 2015 at 10:00
I compared now the two results (one with dir $Privat... and the other with $server.Avail) and found that the variable which works, has 7 NoteProperty entries (DisplayName, PSChildName, PSDrive, PSIsContainer, PSParentPath, PSPath and PSProvider)... Where do they come from??? Why? Confused.... :-( — Dan Stef
– Dan Stef, Commented Oct 15, 2015 at 10:15

Dan Stef · Accepted Answer · 2015-10-15 11:41:04Z

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 ;-)

Collectives™ on Stack Overflow

SQL Powershell: Remove-SqlAvailabilityDatabase

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related