moving linked servers

Last Post 26 Nov 2008 01:20 PM by verenatechie. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

17 May 2007 11:23 AM
search terms:
linked servers
moving linked server
move linked
copy links
copy linked servers

ever just want something quick to move over linked servers
from one server to another?

remember to set the output to 'grid' or press CTRL+D


set nocount on
select 'exec master..sp_addlinkedserver
@server = ''' + srvname + ''',
@srvproduct =', + '''' + srvproduct + ''',
@provider =', + '''' + providername + ''', @datasrc = ' , '''' +
datasource + '''' from sysservers

next just simply run the output.

hope this is useful.
New Member
New Member

17 May 2007 11:50 AM
one more point i forgot to mention.

this will not automatically copy over any remote security connections
so you would have to apply the appropriate logins manually.

this can be found under the security tab of the linked server properties.

if you are using a remote login; then you should see the following option

Be made using the security context.
Remote Login
With Password

just had to mention it.
New Member
New Member

26 Nov 2008 01:20 PM
I ran your script (the output from the commands to run) and got the following error:

Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
You cannot specify a provider or any properties for product 'SQL Server'.

It really doesn't save much time if you have to manually add the login and also update the other properties, however if it worked at least you wouldn't miss one.


Acceptable Use Policy