SQL Server: Linked Server connections: Scripting

Question

I was looking for a way to script existing linked server connections for reference and migration to other servers. I have over 150 of them pointing to Azure SQL Databases used to gather reports.

Answer 1

After a long search I built this. It might be useful for someone doing the same. I added 5 server options but you can obviously add what you need. I hope this helps someone.

DECLARE @preface            varchar(512)
DECLARE @addlinkserver      varchar(512)
DECLARE @addlinksvrlogin    varchar(512)
DECLARE @serveroption01     varchar(512)
DECLARE @serveroption02     varchar(512)
DECLARE @serveroption03     varchar(512)
DECLARE @serveroption04     varchar(512)
DECLARE @serveroption05     varchar(512)

DECLARE @lnkedsvrs TABLE
(
RID INT IDENTITY(1,1) Primary Key
, preface           varchar(512)
, addlinkserver     varchar(512)
, addlinksvrlogin   varchar(512)
, serveroption01        varchar(512)
, serveroption02        varchar(512)
, serveroption03        varchar(512)
, serveroption04        varchar(512)
, serveroption05        varchar(512)
)

INSERT INTO @lnkedsvrs
([preface], [addlinkserver], [addlinksvrlogin], [serveroption01], [serveroption02], [serveroption03], [serveroption04], [serveroption05])

SELECT 
 [preface] = 'USE [master]' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) 
,[addlinkserver] = 'EXEC master.dbo.sp_addlinkedserver @server = N'' ' + ss.name + ''', @srvproduct=N''' + ss.provider + ''', @datasrc=N''' + ss.name +''', @catalog=N'''+ss.catalog+''';'
,[adlinksvrlogin] = 'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N''' + ss.name + ''', @useself=N''False'', @locallogin=NULL, @rmtuser=N'''+ sl.remote_name + ''', @rmtpassword=''########''; ' + CHAR(13) + CHAR(10) + 'GO'
,[serveroption01] = 'EXEC master.dbo.sp_serveroption @server=N''' + ss.name + ''', @optname=N''rpc'', @optvalue=N''true'';'
,[serveroption02] = 'EXEC master.dbo.sp_serveroption @server=N''' + ss.name + ''', @optname=N''rpc out'', @optvalue=N''true'';'
,[serveroption03] = 'EXEC master.dbo.sp_serveroption @server=N''' + ss.name + ''', @optname=N''use remote collation'', @optvalue=N''true'';'
,[serveroption04] = 'EXEC master.dbo.sp_serveroption @server=N''' + ss.name + ''', @optname=N''remote proc transaction promotion'', @optvalue=N''true'';'
,[serveroption05] = 'EXEC master.dbo.sp_serveroption @server=N''' + ss.name + ''', @optname=N''data access'', @optvalue=N''true'';'
    FROM sys.Servers ss 
        LEFT JOIN sys.linked_logins sl 
            ON ss.server_id = sl.server_id 
        LEFT JOIN sys.server_principals ssp 
            ON ssp.principal_id = sl.local_principal_id
    WHERE ss.product  = 'Azure SQL Db'
    ORDER BY ss.name ASC

DECLARE @CurRow             int = 1 
DECLARE @MaxRow             int

SELECT @MaxRow  = MAX(RID)
    FROM @lnkedsvrs

WHILE (@CurRow <= @MaxRow)
    BEGIN
        SELECT @preface     = preface       
        ,@addlinkserver     = addlinkserver 
        ,@addlinksvrlogin   = addlinksvrlogin
        ,@serveroption01    = serveroption01
        ,@serveroption02    = serveroption02
        ,@serveroption03    = serveroption03
        ,@serveroption04    = serveroption04
        ,@serveroption05    = serveroption05
            FROM @lnkedsvrs
            WHERE RID = @CurRow
    
        print @preface
        print @addlinkserver
        print @addlinksvrlogin
        print @serveroption01
        print @serveroption02
        print @serveroption03
        print @serveroption04
        print @serveroption05
        print 'GO'+CHAR(13) + CHAR(10)
        print CHAR(13) + CHAR(10)

        SET @CurRow = @CurRow + 1
    END