functions/Copy-SqlOperator.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
Function Copy-SqlOperator
{
<#
.SYNOPSIS
Copy-SqlOperator migrates operators from one SQL Server to another.

.DESCRIPTION
By default, all operators are copied. The -Operators parameter is autopopulated for command-line completion and can be used to copy only specific operator s.

If the associated credential for the does not exist on the destination, it will be skipped. If the operator already exists on the destination, it will be skipped unless -Force is used.

.EXAMPLE
Copy-SqlOperator -Source sqlserver2014a -Destination sqlcluster

Copies all operators from sqlserver2014a to sqlcluster, using Windows credentials. If operators with the same name exist on sqlcluster, they will be skipped.

.EXAMPLE
Copy-SqlOperator -Source sqlserver2014a -Destination sqlcluster -Operator PSOperator -SourceSqlCredential $cred -Force

Copies a single operator, the PSOperator operator from sqlserver2014a to sqlcluster, using SQL credentials for sqlserver2014a
and Windows credentials for sqlcluster. If an operator with the same name exists on sqlcluster, it will be dropped and recreated because -Force was used.

.EXAMPLE
Copy-SqlOperator -Source sqlserver2014a -Destination sqlcluster -WhatIf -Force

Shows what would happen if the command were executed using force.
#>

    [CmdletBinding(DefaultParameterSetName = "Default", SupportsShouldProcess = $true)]
    param (
        [parameter(Mandatory = $true)]
        [object]$Source,
        [parameter(Mandatory = $true)]
        [object]$Destination,
        [System.Management.Automation.PSCredential]$SourceSqlCredential,
        [System.Management.Automation.PSCredential]$DestinationSqlCredential,
        [switch]$Force
    )
    DynamicParam { if ($source) { return (Get-ParamSqlOperators -SqlServer $Source -SqlCredential $SourceSqlCredential) } }
    
    BEGIN {

        $operators = $psboundparameters.Operators

        $sourceserver = Connect-SqlServer -SqlServer $Source -SqlCredential $SourceSqlCredential
        $destserver = Connect-SqlServer -SqlServer $Destination -SqlCredential $DestinationSqlCredential
        
        $serveroperators = $sourceserver.JobServer.Operators
        $destoperators = $destserver.JobServer.Operators
        
        $failsafe = $server.JobServer.AlertSystem | Select FailSafeOperator

    }

    PROCESS
    {

        foreach ($operator in $serveroperators)
        {
            $operatorname = $operator.name
            if ($operators.length -gt 0 -and $operators -notcontains $operatorname) { continue }
            
            if ($destoperators.name -contains $operator.name)
            {
                if ($force -eq $false)
                {
                    Write-Warning "Operator $operatorname exists at destination. Use -Force to drop and migrate."
                    continue
                }
                else
                {
                    if ($failsafe.FailSafeOperator -eq $operatorname)
                    {
                        Write-Warning "$operatorname is the failsafe operator. Skipping drop."
                        continue
                    }
                    
                    If ($Pscmdlet.ShouldProcess($destination, "Dropping operator $operatorname and recreating"))
                    {
                        try
                        {
                            Write-Verbose "Dropping Operator $operatorname"
                            $destserver.jobserver.operators[$operatorname].Drop()
                        }
                        catch 
                        { 
                            Write-Exception $_ 
                            continue
                        }
                    }
                }
            }

            If ($Pscmdlet.ShouldProcess($destination, "Creating Operator $operatorname"))
            {
                try
                {
                    Write-Output "Copying Operator $operatorname"
                    $sql = $operator.Script() | Out-String
                    $sql = $sql -replace [Regex]::Escape("'$source'"), [Regex]::Escape("'$destination'")
                    Write-Verbose $sql
                    $destserver.ConnectionContext.ExecuteNonQuery($sql) | Out-Null
                }
                catch
                {
                    Write-Exception $_
                }
            }
        }
    }
    
    END
    {
        $sourceserver.ConnectionContext.Disconnect()
        $destserver.ConnectionContext.Disconnect()
        If ($Pscmdlet.ShouldProcess("console", "Showing finished message")) { Write-Output "Operator migration finished" }
    }
}