Generate scripts for Linked Servers

Couple of days back one of my team members was working on a project of moving all linked servers from one server to another. He checked with me if I had any scripts available.

I had a copy of Chad Miller’s script on SQL Server Central and Sriram Chitturi’s GUI Tool. These two were very useful in SQL Server 2000 era where SQL Server did not have the provision to export Linked Servers. Sriram’s tool works on all version of SQL Server. These will generate a SQL script of all the Linked Servers configured on a given instance. Executing the output on the destination instance will create the Linked Servers.

Starting SQL Server 2005, the ability to script a Linked Server is built-in SSMS. Right clicking on a Linked Server provides one with the option to generate a script out of it. But in SSMS the script for all Linked Servers cannot be generated at once.

The only drawback with all these approaches (drawback is not the right word, it is a security feature) is that the Remote Passwords entered in the Linked Server are not exported. After executing the script the password for these accounts needs to be manually entered in linked server.

Even the script generated informs that the password were not exported.

Yesterday I checked with him about the progress of that project. He told me that since there were only few linked servers, he chose to create the Linked Servers manually. However he is now equipped with the necessary information for future reference.

2 thoughts on “Generate scripts for Linked Servers

  1. Chad Miller

    Since SSMS only allows scripting of a single linked server at a time, PowerShell provides an interesting solution.

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

    It still doesn’t script passwords, but this is useful if you have more than a few linked servers.

Comments are closed.