PoshCode Archive  Artifact [944b4f016b]

Artifact 944b4f016bc366fae5fac605605f3a5b7f3498c9f9de3ef2b2734d0357f88173:

  • File LibraryLinkedServer.ps1 — part of check-in [ed7fea70dc] at 2018-06-10 13:19:28 on branch trunk — Filters for backing and removing SQL Server linked servers or linked server login mappings (user: Chad Miller size: 7936)

# encoding: ascii
# api: powershell
# title: LibraryLinkedServer
# description: Filters for backing and removing SQL Server linked servers or linked server login mappings
# version: 1.0
# type: script
# author: Chad Miller
# license: CC0
# function: Get-CMRegisteredServer
# x-poshcode-id: 3048
# x-archived: 2011-11-16T01:41:07
# x-published: 2011-11-13T07:19:00
#
#
try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}

try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"; $smoVersion = 9}

try {add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
catch {add-type -AssemblyName "Microsoft.SqlServer.SqlEnum"; $smoVersion = 9}

try
{
    try {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
    catch {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended" -EA Stop}
}
catch {Write-Warning "SMOExtended not available"}

$ErrorActionPreference = 'Stop'

$scriptRoot = Split-Path (Resolve-Path $myInvocation.MyCommand.Path)

#######################
filter Remove-LinkedServerLogin
{
    param([string[]]$LinkedServerLogins)

    $ServerInstance = $_
    $sqlserver = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerInstance 
    #Get the linked server logins in the global group
    $l = $sqlserver.LinkedServers| % {$_.LinkedServerLogins } | ? {$LinkedServerLogins -contains $_.name}
    write-host $ServerInstance
    if ($l) {
        #Drop the Linked Server Logins
        $l | %{$_.Drop()}
    }

} #Remove-LinkedServerLogin

#######################
filter Backup-LinkedServer
{
    param($LinkedServer,[string[]]$LinkedServerLogins)

    $ServerInstance = $_
    $ServerInstanceFileName = $ServerInstance -replace '\\','_'
    $sqlserver = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerInstance 

    #Get the linked servers
    if ($LinkedServer) {
        $l = $sqlserver.LinkedServers| ? {$_.Name -eq "$LinkedServer" } 
    }
    elseif ($LinkedServerLogins) {
        $l = $sqlserver.LinkedServers| % {$_.LinkedServerLogins } | ? {$LinkedServerLogins -contains $_.name} | %{$_.parent} | select-object -unique
    }
    else {
        throw 'LinkedServer or LinkedServerLogins required.'
    }

    write-host $ServerInstance
    $opts = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
    $opts.ToFileOnly =$true
   if ($l) {
        #Backup Linked Server Before Dropping Linked Server Logins
    $l | % {$opts.FileName = $("{0}\{1}_{2}.sql" -f $scriptRoot,$ServerInstanceFileName,$($($_.Name) -replace '\\','_')); write-host $opts.FileName; $_.script($opts)}
    }

} #Backup-LinkedServer

#######################
filter Remove-LinkedServer
{
    param($LinkedServer)

    $ServerInstance = $_
    $ServerInstanceFileName = $ServerInstance -replace '\\','_'
    $sqlserver = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerInstance 

    write-host "$ServerInstance"
    $l = $sqlserver.LinkedServers| ? {$_.Name -eq "$LinkedServer" } 
    #Backup Linked Server Before Dropping
    if ($l) {
        #Drop the Linked Server
        $l | %{$_.Drop($true)}
    }

} #Remove-LinkedServer

#######################
function Get-CMRegisteredServer
{
    param($CMServer,$GroupName)

$query = @"
SELECT DISTINCT s.name
FROM msdb.dbo.sysmanagement_shared_registered_servers s
JOIN msdb.dbo.sysmanagement_shared_server_groups g
ON s.server_group_id = g.server_group_id
WHERE 1 = 1
"@

    if ($GroupName) {
    $query =+ "`nAND g.name = '$GroupName'"
    }

    #Write-Host $query
    Invoke-SqlCmd2 -ServerInstance $CMServer -Database msdb -Query $query | foreach {$_.name}

} #Get-CMRegisteredServer

#######################
<#
.SYNOPSIS
Runs a T-SQL script.
.DESCRIPTION
Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified
.INPUTS
None
    You cannot pipe objects to Invoke-Sqlcmd2
.OUTPUTS
   System.Data.DataTable
.EXAMPLE
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1"
This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.
StartTime
-----------
2010-08-12 21:21:03.593
.EXAMPLE
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"
This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.
.EXAMPLE
Invoke-Sqlcmd2  -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose
This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
VERBOSE: hello world
.NOTES
Version History
v1.0   - Chad Miller - Initial release
v1.1   - Chad Miller - Fixed Issue with connection closing
v1.2   - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation
v1.3   - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type
#>
function Invoke-Sqlcmd2
{
    [CmdletBinding()]
    param(
    [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
    [Parameter(Position=1, Mandatory=$false)] [string]$Database,
    [Parameter(Position=2, Mandatory=$false)] [string]$Query,
    [Parameter(Position=3, Mandatory=$false)] [string]$Username,
    [Parameter(Position=4, Mandatory=$false)] [string]$Password,
    [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600,
    [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15,
    [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile,
    [Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow"
    )

    if ($InputFile)
    {
        $filePath = $(resolve-path $InputFile).path
        $Query =  [System.IO.File]::ReadAllText("$filePath")
    }

    $conn=new-object System.Data.SqlClient.SQLConnection
     
    if ($Username)
    { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
    else
    { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }

    $conn.ConnectionString=$ConnectionString
    
    #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
    if ($PSBoundParameters.Verbose)
    {
        $conn.FireInfoMessageEventOnUserErrors=$true
        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"}
        $conn.add_InfoMessage($handler)
    }
    
    $conn.Open()
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
    $cmd.CommandTimeout=$QueryTimeout
    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.fill($ds)
    $conn.Close()
    switch ($As)
    {
        'DataSet'   { Write-Output ($ds) }
        'DataTable' { Write-Output ($ds.Tables) }
        'DataRow'   { Write-Output ($ds.Tables[0]) }
    }

} #Invoke-Sqlcmd2