internal/DynamicParams.ps1
|
<#
These are all the functions for tab completion (auto-population of params) To use, place this after params in a function DynamicParam { if ($source) { return (Get-ParamSqlXyz -SqlServer $Source -SqlCredential $SourceSqlCredential) } } #> Function Get-ParamSqlServerConfigs { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Server Configs from specified SQL Server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $configlist = @() $server.Configuration.ShowAdvancedOptions.ConfigValue = $true $null = $server.ConnectionContext.ExecuteNonQuery("RECONFIGURE WITH OVERRIDE") $configlist = $server.Configuration.PsObject.Properties.Name | Where-Object { $_ -notin "Parent", "Properties" } $server.Configuration.ShowAdvancedOptions.ConfigValue = $false $null = $server.ConnectionContext.ExecuteNonQuery("RECONFIGURE WITH OVERRIDE") # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($configlist) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $configlist } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($configlist) { $attributeCollection.Add($validationset) } $Configs = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Configs", [String[]], $attributeCollection) $newparams.Add("Configs", $Configs) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlDatabases { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with database list from specified SQL Server server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential, [switch]$NoSystem, [switch]$DbsWithSnapshotsOnly ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } $SupportDbs = "ReportServer", "ReportServerTempDb", "distribution" # Populate arrays $databaselist = @() if ($server.Databases.Count -gt 255) { # Don't slow them down by building a list that likely won't be used anyway $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $Databases = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Databases", [String[]], $attributes) $newparams.Add("Databases", $Databases) $Exclude = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Exclude", [String[]], $attributes) $newparams.Add("Exclude", $Exclude) return $newparams } foreach ($database in $server.databases) { if ($NoSystem) { if (!($database.IsSystemObject) -and $SupportDbs -notcontains $database.name) { $databaselist += $database.name } } elseif ($DbsWithSnapshotsOnly) { if ($database.DatabaseSnapshotBaseName.Length -gt 0) { $databaselist += $database.DatabaseSnapshotBaseName } } else { $databaselist += $database.name } } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary # Provide backwards compatability for improperly named parameter # Scratch that. I'm going with plural. Sorry, Snoves! $alias = New-Object System.Management.Automation.AliasAttribute "Database" $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($databaselist) { $dbvalidationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $databaselist } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($databaselist) { $attributeCollection.Add($dbvalidationset) } $attributeCollection.Add($alias) $Databases = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Databases", [String[]], $attributeCollection) $eattributes = New-Object System.Management.Automation.ParameterAttribute $eattributes.ParameterSetName = "__AllParameterSets" $eattributes.Mandatory = $false $eattributes.Position = 20 $dbexcludeattributes = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $dbexcludeattributes.Add($eattributes) if ($databaselist) { $dbexcludeattributes.Add($dbvalidationset) } $Exclude = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Exclude", [String[]], $dbexcludeattributes) $newparams.Add("Databases", $Databases) $newparams.Add("Exclude", $Exclude) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlDatabase { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with database list from specified SQL Server server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential, [switch]$DbsWithSnapshotsOnly ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $databaselist = @() if ($server.Databases.Count -gt 255) { # Don't slow them down by building a list that likely won't be used anyway $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $Database = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Database", [String], $attributes) $newparams.Add("Database", $Database) return $newparams } if ($DbsWithSnapshotsOnly) { $databaselist = ($server.databases | Where-Object { $_.DatabaseSnapshotBaseName.Length -gt 0 }).DatabaseSnapshotBaseName } else { $databaselist = $server.databases.name } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($databaselist) { $dbvalidationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $databaselist } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($databaselist) { $attributeCollection.Add($dbvalidationset) } $attributeCollection.Add($alias) $Database = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Database", [String], $attributeCollection) $newparams.Add("Database", $Database) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlSnapshotsAndDatabases { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with snapshot list from specified SQL Server server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $databaselist = $snapshotlist = @() if ($server.Databases.Count -gt 255) { # Don't slow them down by building a list that likely won't be used anyway $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $Snapshots = New-Object -TypeName System.Management.Automation.RuntimeDefinedParameter("Snapshots", [String[]], $attributes) $newparams.Add("Snapshots", $Snapshots) $Databases = New-Object -TypeName System.Management.Automation.RuntimeDefinedParameter("Databases", [String[]], $attributes) $newparams.Add("Databases", $Databases) return $newparams } foreach ($database in $server.databases) { if ($database.IsDatabaseSnapshot) { $snapshotlist += $database.name } if ($database.DatabaseSnapshotBaseName.Length -gt 0) { $databaselist += $database.DatabaseSnapshotBaseName } } $databaselist = $databaselist | Get-Unique # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false # Database list parameter setup if ($databaselist) { $dbvalidationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $databaselist } $attributeCollection = New-Object -TypeName System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($databaselist) { $attributeCollection.Add($dbvalidationset) } $attributeCollection.Add($alias) $Databases = New-Object -TypeName System.Management.Automation.RuntimeDefinedParameter("Databases", [String[]], $attributeCollection) $newparams.Add("Databases", $Databases) # Database list parameter setup if ($snapshotlist) { $dbvalidationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $snapshotlist } $attributeCollection = New-Object -TypeName System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($snapshotlist) { $attributeCollection.Add($dbvalidationset) } $Snapshots = New-Object -TypeName System.Management.Automation.RuntimeDefinedParameter("Snapshots", [String[]], $attributeCollection) $newparams.Add("Snapshots", $Snapshots) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlLogins { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with login list from specified SQL Server server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential, [switch]$WindowsOnly, [switch]$SqlOnly ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } if ($server.logins.count -gt 255) { # Don't slow them down by building a list that likely won't be used anyway $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $Logins = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Logins", [String[]], $attributes) $newparams.Add("Logins", $Logins) $Exclude = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Exclude", [String[]], $attributes) $newparams.Add("Exclude", $Exclude) return $newparams } $loginlist = @() foreach ($login in $server.logins) { if (!$login.name.StartsWith("##") -and $login.name -ne 'sa') { if ($WindowsOnly) { if ($login.LoginType -eq 'WindowsUser' -or $login.LoginType -eq 'WindowsGroup') { $loginlist += $login.name } } elseif ($SqlOnly) { if ($login.LoginType -eq 'SqlLogin') { $loginlist += $login.name } } else { $loginlist += $login.name } } } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute # Provide backwards compatability for improperly named parameter # Scratch that. I'm going with plural. Sorry, Snoves! $alias = New-Object System.Management.Automation.AliasAttribute "Login" $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Login list parameter setup if ($loginlist) { $loginvalidationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $loginlist } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($loginlist) { $attributeCollection.Add($loginvalidationset) } $attributeCollection.Add($alias) $Logins = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Logins", [String[]], $attributeCollection) $excludeattributes = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $excludeattributes.Add($attributes) if ($loginlist) { $excludeattributes.Add($loginvalidationset) } $Exclude = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Exclude", [String[]], $excludeattributes) $newparams.Add("Logins", $Logins) $newparams.Add("Exclude", $Exclude) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlLogin { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with login list from specified SQL Server server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential, [switch]$WindowsOnly, [switch]$SqlOnly ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } $loginlist = @() foreach ($login in $server.logins) { if (!$login.name.StartsWith("##")) { if ($WindowsOnly) { if ($login.LoginType -eq 'WindowsUser' -or $login.LoginType -eq 'WindowsGroup') { $loginlist += $login.name } } elseif ($SqlOnly) { if ($login.LoginType -eq 'SqlLogin') { $loginlist += $login.name } } else { $loginlist += $login.name } } } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute # Provide backwards compatability for improperly named parameter # Scratch that. I'm going with plural. Sorry, Snoves! $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $true # Login list parameter setup if ($loginlist) { $loginvalidationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $loginlist } $attributeCollection = New-Object -TypeName System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($loginlist) { $attributeCollection.Add($loginvalidationset) } $Login = New-Object -TypeName System.Management.Automation.RuntimeDefinedParameter("Login", [String], $attributeCollection) $newparams.Add("Login", $Login) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlServerRoles { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Server Roles from specified SQL Server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $rolelist = @() $roles = $server.roles | Where-Object { $_.IsFixedRole -eq $false -and $_.Name -ne 'public' } foreach ($role in $roles) { $rolelist += $role.name } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($rolelist) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $rolelist } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($rolelist) { $attributeCollection.Add($validationset) } $roles = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Roles", [String[]], $attributeCollection) $newparams.Add("Roles", $roles) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlProcessHosts { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $items = $server.EnumProcesses().Host | Where-Object { $_.Length -gt 1 } | Sort-Object | Get-Unique # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($items) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $items } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($items) { $attributeCollection.Add($validationset) } $Hosts = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Hosts", [String[]], $attributeCollection) $newparams.Add("Hosts", $Hosts) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlProcessPrograms { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $items = $server.EnumProcesses().Program | Where-Object { $_.Length -gt 1 } | Sort-Object | Get-Unique # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($items) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $items } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($items) { $attributeCollection.Add($validationset) } $Programs = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Programs", [String[]], $attributeCollection) $newparams.Add("Programs", $Programs) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlSpids { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $spids = $server.EnumProcesses().Spid | Where-Object { $_ -gt 50 } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false # Database list parameter setup if ($spids) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $spids } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($spids) { $attributeCollection.Add($validationset) } $Spids = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Spids", [String[]], $attributeCollection) $Exclude = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Exclude", [String[]], $attributeCollection) $newparams.Add("Spids", $Spids) $newparams.Add("Exclude", $Exclude) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlCredentials { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with SQL Credentials from specified SQL Server server name. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $credentiallist = @() foreach ($credential in $server.credentials) { $credentiallist += $credential.name } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($credentiallist) { $dbvalidationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $credentiallist } $lsattributes = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $lsattributes.Add($attributes) if ($credentiallist) { $lsattributes.Add($dbvalidationset) } $Credentials = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Credentials", [String[]], $lsattributes) $newparams.Add("Credentials", $Credentials) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlServerAudits { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Server Audits from specified SQL Server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $auditlist = @() foreach ($audit in $server.audits) { $auditlist += $audit.name } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($auditlist) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $auditlist } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($auditlist) { $attributeCollection.Add($validationset) } $audits = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Audits", [String[]], $attributeCollection) $newparams.Add("Audits", $audits) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlServerServerAuditSpecifications { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Server ServerAuditSpecifications from specified SQL Server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $auditspeclist = @() foreach ($auditspec in $server.ServerAuditSpecifications) { $auditspeclist += $auditspecname } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($auditspeclist) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $auditspeclist } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($auditspeclist) { $attributeCollection.Add($validationset) } $serverAuditSpecifications = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("ServerAuditSpecifications", [String[]], $attributeCollection) $newparams.Add("ServerAuditSpecifications", $serverAuditSpecifications) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlBackupDevices { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Backup Devices from specified SQL Server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $backupdevicelist = @() foreach ($backupdevice in $server.BackupDevices) { $backupdevicelist += $backupdevice.name } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($backupdevicelist) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $backupdevicelist } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($backupdevicelist) { $attributeCollection.Add($validationset) } $backupdevices = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("BackupDevices", [String[]], $attributeCollection) $newparams.Add("BackupDevices", $backupdevices) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlServerEndpoints { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Server Endpoints from specified SQL Server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $endpointlist = @() $usernedponit = $server.Endpoints | Where-Object { $_.IsSystemObject -eq $false } foreach ($endpoint in $server.Endpoints) { $endpointlist += $endpointname } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($endpointlist) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $endpointlist } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($endpointlist) { $attributeCollection.Add($validationset) } $Endpoints = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Endpoints", [String[]], $attributeCollection) $newparams.Add("Endpoints", $Endpoints) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlLinkedServers { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Linked Servers from specified server name. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $linkedserverlist = @() foreach ($linkedserver in $server.LinkedServers) { # skip the replication linked server if ($linkedserver.name -ne 'repl_distributor') { $linkedserverlist += $linkedserver.name } } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($linkedserverlist) { $dbvalidationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $linkedserverlist } $lsattributes = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $lsattributes.Add($attributes) if ($linkedserverlist) { $lsattributes.Add($dbvalidationset) } $LinkedServers = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("LinkedServers", [String[]], $lsattributes) $newparams.Add("LinkedServers", $LinkedServers) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlPolicyManagement { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Sql Policy Management objects from specified SQL Server server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } if ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Dmf") -eq $null) { return } $sqlconn = $server.ConnectionContext.SqlConnectionObject $sqlStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection $sqlconn # DMF is the Declarative Management Framework, Policy Based Management's old name $store = New-Object Microsoft.SqlServer.Management.DMF.PolicyStore $sqlStoreConnection $objects = "Policies", "Conditions" # Maybe other stuff later? I don't know PBM well enough yet to know. $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 foreach ($name in $objects) { $items = $store.$name.Name if ($items.count -gt 0) { $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) $attributeCollection.Add((New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $items)) } $newparams.Add($name, (New-Object -Type System.Management.Automation.RuntimeDefinedParameter($name, [String[]], $attributeCollection))) } $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlResourceGovernor { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Resource Governor objects from specified SQL Server server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } $pools = $server.ResourceGovernor.ResourcePools | Where-Object { $_.Name -notin "internal", "default" } if ($pools.count -gt 0) { $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) $attributeCollection.Add((New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $pools.Name)) $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $newparams.Add("ResourcePools", (New-Object -Type System.Management.Automation.RuntimeDefinedParameter("ResourcePools", [String[]], $attributeCollection))) } $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlExtendedEvents { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Extended Event objects from specified SQL Server server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) if ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.XEvent") -eq $null) { return } try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } $sqlconn = $server.ConnectionContext.SqlConnectionObject $sqlStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection $sqlconn $store = New-Object Microsoft.SqlServer.Management.XEvent.XEStore $sqlStoreConnection $objects = "Sessions" # Maybe packages later? I don't understand xEvents well enough yet to know. $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 foreach ($name in $objects) { $items = $store.$name.Name if ($items.count -gt 0) { $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) $attributeCollection.Add((New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $items)) } $newparams.Add($name, (New-Object -Type System.Management.Automation.RuntimeDefinedParameter($name, [String[]], $attributeCollection))) } $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlDatabaseMail { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Database Mail server objects from specified SQL Server server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } $objects = "ConfigurationValues", "Profiles", "Accounts", "MailServers" $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 foreach ($name in $objects) { if ($name -eq "MailServers") { $items = $server.Mail.Accounts.$name.Name } else { $items = $server.Mail.$name.Name } if ($items.count -gt 0) { $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) $attributeCollection.Add((New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $items)) } $newparams.Add($name, (New-Object -Type System.Management.Automation.RuntimeDefinedParameter($name, [String[]], $attributeCollection))) } $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlJobServer { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with job server objects from specified SQL Server server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } $jobobjects = "ProxyAccounts", "JobSchedule", "SharedSchedules", "AlertSystem", "JobCategories", "OperatorCategories" $jobobjects += "AlertCategories", "Alerts", "TargetServerGroups", "TargetServers", "Operators", "Jobs", "Mail" $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 foreach ($name in $jobobjects) { $items = $server.JobServer.$name.Name if ($items.count -gt 0) { $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) $attributeCollection.Add((New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $items)) } $newparams.Add($name, (New-Object -Type System.Management.Automation.RuntimeDefinedParameter($name, [String[]], $attributeCollection))) } $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlCmsGroups { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with server groups from specified SQL Server Central Management server name. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.RegisteredServers") -eq $null) { return } try { $SqlCms = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } $sqlconnection = $SqlCms.ConnectionContext.SqlConnectionObject try { $cmstore = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($sqlconnection) } catch { return } if ($cmstore -eq $null) { return } # unfortunately the logic is quite cumbersome because group names accept also the '\' # character, that is also the de-facto standard to give a hierarchy. # e.g. # # cms # +--foo # +--bar\baz # +--foo # +--registered server # The only short-circuit would be something like: # cms # +--foo # | +--bar # +--foo\bar Function Parse-CmsGroup($CmsGrp, $base = '') { $results = @() foreach($el in $CmsGrp) { if($base -eq ''){ $partial = $el.name } else { $partial = "$base\$($el.name)" } $results += $partial foreach($group in $el.ServerGroups) { $results += Parse-CmsGroup $group $partial } } return $results } $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $paramattributes = New-Object System.Management.Automation.ParameterAttribute $paramattributes.ParameterSetName = "__AllParameterSets" $paramattributes.Mandatory = $false $paramattributes.Position = 3 $argumentlist = Parse-CmsGroup $cmstore.DatabaseEngineServerGroup.ServerGroups if ($argumentlist -ne $null) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $argumentlist $combinedattributes = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $combinedattributes.Add($paramattributes) $combinedattributes.Add($validationset) $SqlCmsGroups = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("SqlCmsGroups", [String[]], $combinedattributes) $newparams.Add("SqlCmsGroups", $SqlCmsGroups) $Group = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Group", [String[]], $combinedattributes) $newparams.Add("Group", $Group) return $newparams } else { return } } Function Get-ParamSqlServerTriggers { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Server Triggers from specified SQL Server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $triggerlist = @() foreach ($trigger in $server.Triggers) { $triggerlist += $trigger.name } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($triggerlist) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $triggerlist } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($triggerlist) { $attributeCollection.Add($validationset) } $Triggers = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Triggers", [String[]], $attributeCollection) $newparams.Add("Triggers", $Triggers) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlCustomErrors { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with ID of Server Custom Errors/User Defined Messages from specified SQL Server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $messagelist = @() $uniquemessageid = $server.UserDefinedMessages | Select-Object ID | Sort-Object | Get-Unique foreach ($message in $uniquemessageid) { $messagelist += $message.ID } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($messagelist) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $messagelist } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($messagelist) { $attributeCollection.Add($validationset) } $CustomErrors = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("CustomErrors", [String[]], $attributeCollection) $newparams.Add("CustomErrors", $CustomErrors) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlDatabaseAssemblies { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with assemblies from specified SQL Server. Assembly name is in database.assemblyname format. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } ######### Assemblies $list = @() foreach ($database in $server.Databases) { try { # a bug here requires a try/catch $userAssemblies = $($database.assemblies | Where-Object { $_.isSystemObject -eq $false }) foreach ($assembly in $userAssemblies) { $name = "$($database.name).$($assembly.name)" $list += $name } } catch { } } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $Assemblies = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Assemblies", [String[]], $attributeCollection) $newparams.Add("Assemblies", $Assemblies) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlDataCollectionSets { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Collection Sets from specified SQL Server's Data Collection object. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } if ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Collector") -eq $null) { return } $sqlconn = $server.ConnectionContext.SqlConnectionObject $storeconn = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection $sqlconn $store = New-Object Microsoft.SqlServer.Management.Collector.CollectorConfigStore $storeconn # Populate arrays $list = @() $collectionsets = $store.CollectionSets | Where-Object { $_.isSystem -eq $false } foreach ($collectionset in $collectionsets) { $list += $collectionset.name } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $CollectionSets = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("CollectionSets", [String[]], $attributeCollection) $newparams.Add("CollectionSets", $CollectionSets) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlAlerts { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Alerts from specified SQL Server Job Server (SQL Agent). #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $list = $server.JobServer.Alerts.Name # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $Alerts = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Alerts", [String[]], $attributeCollection) $newparams.Add("Alerts", $Alerts) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlOperators { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Operators from specified SQL Server Job Server (SQL Agent). #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $list = $server.JobServer.Operators.Name # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $Operators = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Operators", [String[]], $attributeCollection) $newparams.Add("Operators", $Operators) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlOperatorCategories { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with OperatorCategories from specified SQL Server Job Server (SQL Agent). #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $list = ($server.JobServer.OperatorCategories | Where-Object { $_.ID -ge 100 }).Name # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $OperatorCategories = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("OperatorCategories", [String[]], $attributeCollection) $newparams.Add("OperatorCategories", $OperatorCategories) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlProxyAccounts { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with ProxyAccounts from specified SQL Server Job Server (SQL Agent). #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $list = $server.JobServer.ProxyAccounts.Name # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $ProxyAccounts = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("ProxyAccounts", [String[]], $attributeCollection) $newparams.Add("ProxyAccounts", $ProxyAccounts) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlSharedSchedules { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with SharedSchedules from specified SQL Server Job Server (SQL Agent). #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $list = $server.JobServer.SharedSchedules.Name # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $SharedSchedules = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("SharedSchedules", [String[]], $attributeCollection) $newparams.Add("SharedSchedules", $SharedSchedules) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlJobCategories { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with JobCategories from specified SQL Server Job Server (SQL Agent). #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $list = ($server.JobServer.JobCategories | Where-Object { $_.ID -ge 100 }).Name # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $JobCategories = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("JobCategories", [String[]], $attributeCollection) $newparams.Add("JobCategories", $JobCategories) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlAlertCategories { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with AlertCategories from specified SQL Server Job Server (SQL Agent). #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $list = ($server.JobServer.AlertCategories | Where-Object { $_.ID -ge 100 }).Name # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $AlertCategories = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("AlertCategories", [String[]], $attributeCollection) $newparams.Add("AlertCategories", $AlertCategories) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlTargetServers { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with TargetServers from specified SQL Server Job Server (SQL Agent). #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $list = $server.JobServer.TargetServers.Name # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $TargetServers = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("TargetServers", [String[]], $attributeCollection) $newparams.Add("TargetServers", $TargetServers) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlTargetServerGroups { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with TargetServerGroups from specified SQL Server Job Server (SQL Agent). #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $list = $server.JobServer.TargetServerGroups.Name # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $TargetServerGroups = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("TargetServerGroups", [String[]], $attributeCollection) $newparams.Add("TargetServerGroups", $TargetServerGroups) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlJobs { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Jobs from specified SQL Server Job Server (SQL Agent). #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays $list = $server.JobServer.Jobs.Name # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $Jobs = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Jobs", [String[]], $attributeCollection) $Exclude = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Exclude", [String[]], $attributeCollection) $newparams.Add("Jobs", $Jobs) $newparams.Add("Exclude", $Exclude) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlAgentCategories { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with job server objects from specified SQL Server server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } $jobobjects = "JobCategories", "OperatorCategories", "AlertCategories" $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 foreach ($name in $jobobjects) { $items = ($server.JobServer.$name | Where-Object { $_.ID -ge 100 }).Name if ($items.count -gt 0) { $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) $attributeCollection.Add((New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $items)) } $newparams.Add($name, (New-Object -Type System.Management.Automation.RuntimeDefinedParameter($name, [String[]], $attributeCollection))) } return $newparams } Function Get-ParamSqlDatabaseFileTypes { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Server Configs from specified SQL Server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } # Populate arrays if ($server.versionMajor -eq 8) { $sql = "select distinct CASE WHEN groupid = 1 THEN 'ROWS' WHEN groupid = 0 THEN 'LOG' END as filetype from sysaltfiles" } else { $sql = "SELECT distinct CASE type_desc WHEN 'ROWS' then 'DATA' ELSE type_desc END AS FileType FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id" } $dbfiletable = $server.ConnectionContext.ExecuteWithResults($sql) $filetypes = ($dbfiletable.Tables[0].Rows).FileType if ($server.versionMajor -eq 8) { $filetypes += "FULLTEXT" } # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "FileTypes" $attributes.Mandatory = $true $attributes.Position = 4 # Datatypes list parameter setup if ($filetypes) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $filetypes } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($filetypes) { $attributeCollection.Add($validationset) } $FileType = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("FileType", [String[]], $attributeCollection) $newparams.Add("FileType", $FileType) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlAllProcessInfo { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with job server objects from specified SQL Server server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } $processinfo = "Logins", "Spids", "Exclude", "Hosts", "Programs", "Databases" $processes = $server.EnumProcesses() $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 foreach ($name in $processinfo) { $propertyname = $name.TrimEnd("s") switch ($propertyname) { "Exclude" { $items = $processes.Spid } "Spid" { $items = $processes.Spid } "Database" { $items = $server.Databases.Name } "Login" { $items = $server.Logins.Name $items += ($server.EnumProcesses()).Login $items = $items | Select -Unique } Default { $items = $processes.$propertyname | Where-Object { $_.Length -gt 1 } | Sort-Object | Get-Unique } } if ($items.count -gt 0) { $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) $attributeCollection.Add((New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $items)) } $newparams.Add($name, (New-Object -Type System.Management.Automation.RuntimeDefinedParameter($name, [String[]], $attributeCollection))) } $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlMaintenancePlans { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with Maintenance Plans from specified SQL Server Job Server (SQL Agent). #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } $sql = "SELECT sp.[name] AS MaintenancePlans FROM msdb.dbo.sysmaintplan_plans AS sp" $list = $server.ConnectionContext.ExecuteWithResults($sql).Tables.Rows.MaintenancePlans # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 4 # MaintenancePlan list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $MaintenancePlans = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("MaintenancePlans", [String[]], $attributeCollection) $Exclude = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Exclude", [String[]], $attributeCollection) $newparams.Add("MaintenancePlans", $MaintenancePlans) $newparams.Add("Exclude", $Exclude) $server.ConnectionContext.Disconnect() return $newparams } Function Get-ParamSqlAvailabilityGroups { <# .SYNOPSIS Internal function. Returns System.Management.Automation.RuntimeDefinedParameterDictionary filled with ProxyAccounts from specified SQL Server Job Server (SQL Agent). #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential ) try { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential -ParameterConnection } catch { return } $list = @() # Populate arrays $list = $server.AvailabilityGroups.Name # Reusable parameter setup $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary $attributes = New-Object System.Management.Automation.ParameterAttribute $attributes.ParameterSetName = "__AllParameterSets" $attributes.Mandatory = $false $attributes.Position = 3 # Database list parameter setup if ($list) { $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $list } $attributeCollection = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add($attributes) if ($list) { $attributeCollection.Add($validationset) } $AvailabilityGroups = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("AvailabilityGroups", [String[]], $attributeCollection) $newparams.Add("AvailabilityGroups", $AvailabilityGroups) $server.ConnectionContext.Disconnect() return $newparams } |