PoshCode Logo PowerShell Code Repository

LibraryLinkedServer by Chad Miller 18 months ago
embed code: <script type="text/javascript" src="http://PoshCode.org/embed/3048"></script>download | new post

Filters for backing and removing SQL Server linked servers or linked server login mappings

  1. try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
  2. catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}
  3.  
  4. try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
  5. catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"; $smoVersion = 9}
  6.  
  7. try {add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
  8. catch {add-type -AssemblyName "Microsoft.SqlServer.SqlEnum"; $smoVersion = 9}
  9.  
  10. try
  11. {
  12.     try {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
  13.     catch {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended" -EA Stop}
  14. }
  15. catch {Write-Warning "SMOExtended not available"}
  16.  
  17. $ErrorActionPreference = 'Stop'
  18.  
  19. $scriptRoot = Split-Path (Resolve-Path $myInvocation.MyCommand.Path)
  20.  
  21. #######################
  22. filter Remove-LinkedServerLogin
  23. {
  24.     param([string[]]$LinkedServerLogins)
  25.  
  26.     $ServerInstance = $_
  27.     $sqlserver = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerInstance
  28.     #Get the linked server logins in the global group
  29.     $l = $sqlserver.LinkedServers| % {$_.LinkedServerLogins } | ? {$LinkedServerLogins -contains $_.name}
  30.     write-host $ServerInstance
  31.     if ($l) {
  32.         #Drop the Linked Server Logins
  33.         $l | %{$_.Drop()}
  34.     }
  35.  
  36. } #Remove-LinkedServerLogin
  37.  
  38. #######################
  39. filter Backup-LinkedServer
  40. {
  41.     param($LinkedServer,[string[]]$LinkedServerLogins)
  42.  
  43.     $ServerInstance = $_
  44.     $ServerInstanceFileName = $ServerInstance -replace '\\','_'
  45.     $sqlserver = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerInstance
  46.  
  47.     #Get the linked servers
  48.     if ($LinkedServer) {
  49.         $l = $sqlserver.LinkedServers| ? {$_.Name -eq "$LinkedServer" }
  50.     }
  51.     elseif ($LinkedServerLogins) {
  52.         $l = $sqlserver.LinkedServers| % {$_.LinkedServerLogins } | ? {$LinkedServerLogins -contains $_.name} | %{$_.parent} | select-object -unique
  53.     }
  54.     else {
  55.         throw 'LinkedServer or LinkedServerLogins required.'
  56.     }
  57.  
  58.     write-host $ServerInstance
  59.     $opts = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
  60.     $opts.ToFileOnly =$true
  61.    if ($l) {
  62.         #Backup Linked Server Before Dropping Linked Server Logins
  63.     $l | % {$opts.FileName = $("{0}\{1}_{2}.sql" -f $scriptRoot,$ServerInstanceFileName,$($($_.Name) -replace '\\','_')); write-host $opts.FileName; $_.script($opts)}
  64.     }
  65.  
  66. } #Backup-LinkedServer
  67.  
  68. #######################
  69. filter Remove-LinkedServer
  70. {
  71.     param($LinkedServer)
  72.  
  73.     $ServerInstance = $_
  74.     $ServerInstanceFileName = $ServerInstance -replace '\\','_'
  75.     $sqlserver = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerInstance
  76.  
  77.     write-host "$ServerInstance"
  78.     $l = $sqlserver.LinkedServers| ? {$_.Name -eq "$LinkedServer" }
  79.     #Backup Linked Server Before Dropping
  80.     if ($l) {
  81.         #Drop the Linked Server
  82.         $l | %{$_.Drop($true)}
  83.     }
  84.  
  85. } #Remove-LinkedServer
  86.  
  87. #######################
  88. function Get-CMRegisteredServer
  89. {
  90.     param($CMServer,$GroupName)
  91.  
  92. $query = @"
  93. SELECT DISTINCT s.name
  94. FROM msdb.dbo.sysmanagement_shared_registered_servers s
  95. JOIN msdb.dbo.sysmanagement_shared_server_groups g
  96. ON s.server_group_id = g.server_group_id
  97. WHERE 1 = 1
  98. "@
  99.  
  100.     if ($GroupName) {
  101.     $query =+ "`nAND g.name = '$GroupName'"
  102.     }
  103.  
  104.     #Write-Host $query
  105.     Invoke-SqlCmd2 -ServerInstance $CMServer -Database msdb -Query $query | foreach {$_.name}
  106.  
  107. } #Get-CMRegisteredServer
  108.  
  109. #######################
  110. <#
  111. .SYNOPSIS
  112. Runs a T-SQL script.
  113. .DESCRIPTION
  114. Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified
  115. .INPUTS
  116. None
  117.     You cannot pipe objects to Invoke-Sqlcmd2
  118. .OUTPUTS
  119.    System.Data.DataTable
  120. .EXAMPLE
  121. Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1"
  122. This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.
  123. StartTime
  124. -----------
  125. 2010-08-12 21:21:03.593
  126. .EXAMPLE
  127. Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"
  128. This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.
  129. .EXAMPLE
  130. Invoke-Sqlcmd2  -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose
  131. This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
  132. VERBOSE: hello world
  133. .NOTES
  134. Version History
  135. v1.0   - Chad Miller - Initial release
  136. v1.1   - Chad Miller - Fixed Issue with connection closing
  137. v1.2   - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation
  138. v1.3   - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type
  139. #>
  140. function Invoke-Sqlcmd2
  141. {
  142.     [CmdletBinding()]
  143.     param(
  144.     [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
  145.     [Parameter(Position=1, Mandatory=$false)] [string]$Database,
  146.     [Parameter(Position=2, Mandatory=$false)] [string]$Query,
  147.     [Parameter(Position=3, Mandatory=$false)] [string]$Username,
  148.     [Parameter(Position=4, Mandatory=$false)] [string]$Password,
  149.     [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600,
  150.     [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15,
  151.     [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile,
  152.     [Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow"
  153.     )
  154.  
  155.     if ($InputFile)
  156.     {
  157.         $filePath = $(resolve-path $InputFile).path
  158.         $Query =  [System.IO.File]::ReadAllText("$filePath")
  159.     }
  160.  
  161.     $conn=new-object System.Data.SqlClient.SQLConnection
  162.      
  163.     if ($Username)
  164.     { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
  165.     else
  166.     { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
  167.  
  168.     $conn.ConnectionString=$ConnectionString
  169.    
  170.     #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
  171.     if ($PSBoundParameters.Verbose)
  172.     {
  173.         $conn.FireInfoMessageEventOnUserErrors=$true
  174.         $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"}
  175.         $conn.add_InfoMessage($handler)
  176.     }
  177.    
  178.     $conn.Open()
  179.     $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
  180.     $cmd.CommandTimeout=$QueryTimeout
  181.     $ds=New-Object system.Data.DataSet
  182.     $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
  183.     [void]$da.fill($ds)
  184.     $conn.Close()
  185.     switch ($As)
  186.     {
  187.         'DataSet'   { Write-Output ($ds) }
  188.         'DataTable' { Write-Output ($ds.Tables) }
  189.         'DataRow'   { Write-Output ($ds.Tables[0]) }
  190.     }
  191.  
  192. } #Invoke-Sqlcmd2

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me