Drop Linked Server from All Servers

Scott Newman Powershell, SQL Server August 26, 2014 1 Minute

Quick script on how to drop a linked server from a list of servers. Minimally tested, use at your own risk.

 $LinkedServerName = 'LinkedServerToFindAndDrop'

try{
    gc -Path c:\Servers.txt | %{

        $srvName = $_
	    $srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	    $srvConn.ServerInstance = $srvName
	    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

        $linkedServer = $srv.LinkedServers | where{$_.DataSource -eq $LinkedServerName} 
        if($linkedServer -ne $null){
            for([int]$i = 0; $i -lt $linkedServer.LinkedServerLogins.Count; $i++){
                $login = $linkedServer.LinkedServerLogins.item($i);
                $login.Drop();
            }

            $linkedServer.Drop();
        }
    }

}
catch{
    $_ | fl -Force
}