SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Scripting Linked Servers

A co-worked asked me to look at a T-SQL script I had written 8 years ago for scripting out linked servers and linked server logins from SQL Server. The script wasn’t working as expected. I hadn’t seen the code in some time, but looking at it now the fact that it did not work wasn’t surprising as the code uses some of bad practices like directly querying system tables which I’ve discouraged in previous posts. So rather than fix the T-SQL script, I fired up sqlps PowerShell host and in 5 minutes I had a much simpler and working one-line PowerShell command:
From SQL Server 2008 SSMS navigate to the Linked Servers folder in Object Explorer, right-click and start sqlps (PowerShell):
Script out Linked Servers and logins

PS SQLSERVER:\SQL\Z002\SQL1\LinkedServers get-childitem | %{$_.Script()}

A few things struck me about the PowerShell solution:

  • This isn’t something that can be done from the GUI (SQL Server Management Studio). The functionality simply isn’t there nor should it be. Some advanced things are easier and better exposed in PowerShell than the GUI
  • PowerShell is certainly much easier than my old T-SQL solution.
  • Unlike the T-SQL solution, I doubt I will be asked to fix this simple PowerShell command in another 8 years.

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.


No comments.

Leave a Comment

Please register or log in to leave a comment.