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.