Wednesday, November 19, 2008

How to Transfer or Move or Script or Migrate Linked Servers Settings in SQL Server

Below are some methods available to perform this task

1) Restore Master DB which will create linked servers automatically (Risky in Cluster Environment).
2) Use the exe from http://www.codeproject.com/KB/database/ScriptLinkedServers.aspx to script all settings except password.
3) Try hacking method of copying the password from source syslogins table to remote server from http://www.sqlservercentral.com/Forums/Topic165118-5-1.aspx
4) Use below sample script to get the server name, provider details but no security settings.

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

No comments:

Post a Comment

Please do not spam!