Linked servers

Question

Linked servers

SQLQuest29

SSCrazy Eights

Points: 8264
More actions
October 21, 2009 at 9:26 pm

#134695

Hello All,
How can I script out all linked servers using T SQL? I have tried using GUI (but that is a manual process)
Environment: SQL server 2005, enterprise edition with SP3
The reason to script out is that we are consolidating servers, so the servers which are going out are having linked servers configured. So those linked servers need to be created on new servers.
while creating Linked servers on new servers, any gotchas to be considered ?
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 1

You can use the GUI. In Object Explorer, click on the Linked Servers node and they should be listed in the Object Explorer Details. Select all the Linked Servers in the Details pane and right click. One of options will be to generate scripts. This will generate a script for each linked server. The only caveat is the password will not be there.

There are other ways (SMO in a .Net language or Powershell) also but for a one time event, this is the easiest.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

josantia Ten Centuries Points: 1255 More actions · Answer 2

Hi,

Some time ago, I did an script in Powershell to do that:

http://practicascomunes.blogspot.com/2008/08/utilizando-poweshell-con-sql-2005-smo.html

It's written in spanish, but you can translate in google.

Hope it helps.

Regards.

JSO

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 3

josantia (10/23/2009)
Hi,
Some time ago, I did an script in Powershell to do that:
http://practicascomunes.blogspot.com/2008/08/utilizando-poweshell-con-sql-2005-smo.html
It's written in spanish, but you can translate in google.
Hope it helps.
Regards.
JSO

Thanks for the info. The site is really good and has handy scripts worth for a DBA. I am new to Powershell and am getting to learn it.

I have chosen an old way of scripting using SSMS (rt click and script to a new query window). That works for me as I have to just replace passwords in the scripts.

Any ways thanks for the info.

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

jasonmorris SSCrazy Points: 2492 More actions · Answer 4

SELECT * FROM sys.servers where is_linked = 1 will do it also if you like TSQL.