[MSSQL] 연결된서버(LinkedServer) 스크립트 추출

SQL Server에서 GUI를 바탕으로 연결된서버 Export스크립트화가 가능하지만

스크립트로 Export하는 쿼리를 작성해보았다.

(패스워드는 복호화 불가능함)

 

테스트는 SQL Server 2012, 2019에서 실행

SQL Server 2008 R2이상 버전에서 사용 가능

USE master
GO
 
DECLARE @server_id                INT
DECLARE @name                    NVARCHAR(20)
DECLARE @product                NVARCHAR(20)
DECLARE @provider                NVARCHAR(20)
DECLARE @provider_string        NVARCHAR(100)
DECLARE @data_source            NVARCHAR(20)
DECLARE @catalog                NVARCHAR(50)
DECLARE @clocallogin            NVARCHAR(20)
DECLARE @rmtuser                NVARCHAR(100)
DECLARE @useself                NVARCHAR(100)
DECLARE @locallogin                NVARCHAR(100)
DECLARE @collation_compatible    NVARCHAR(100)
DECLARE @data_access            NVARCHAR(100)
DECLARE @dist                    NVARCHAR(100)
DECLARE @pub                    NVARCHAR(100)
DECLARE @rpc                    NVARCHAR(100)
DECLARE @rpc_out                NVARCHAR(100)
DECLARE @sub                    NVARCHAR(100)
DECLARE @connect_timeout        NVARCHAR(100)
DECLARE @collation_name            NVARCHAR(100)
DECLARE @lazy_schema_validation    NVARCHAR(100)
DECLARE @query_timeout            NVARCHAR(100)
DECLARE @use_remote_collation    NVARCHAR(100)
DECLARE @remote_proc_transaction_promotion    NVARCHAR(100)
 
DECLARE Type_cursor SCROLL CURSOR FOR
SELECT a.[server_id],
    a.name,
    a.product,
    a.[provider],
    CASE WHEN a.provider_string IS NULL THEN 'NULL' ELSE a.provider_string END as provider_string,
    a.[data_source],
    CAST (CASE WHEN a.catalog IS NULL THEN '' ELSE '' + a.catalog + '' END as nvarchar) as catalog,
    CAST (CASE WHEN a.is_collation_compatible = 0 THEN 'false' ELSE 'true' END as nvarchar) as [collation compatible],
    CAST (CASE WHEN a.is_data_access_enabled = 0 THEN 'false' ELSE 'true' END as nvarchar) as [data access],
    CAST (CASE WHEN a.is_distributor = 0 THEN 'false' ELSE 'true' END as nvarchar) as [dist],
    CAST (CASE WHEN a.is_publisher = 0 THEN 'false' ELSE 'true' END as nvarchar) as [pub],
    CAST (CASE WHEN a.is_remote_login_enabled = 0 THEN 'false' ELSE 'true' END as nvarchar) as [rpc],
    CAST (CASE WHEN a.is_rpc_out_enabled = 0 THEN 'false' ELSE 'true' END as nvarchar) as [rpc out],
    CAST (CASE WHEN a.is_subscriber = 0 THEN 'false' ELSE 'true' END as nvarchar) as [sub],
    CAST (CASE WHEN a.connect_timeout = 0 THEN '0' ELSE a.connect_timeout END as nvarchar) as [connect timeout],
    CASE WHEN a.collation_name IS NULL THEN 'null' ELSE '' END as [collation name],
    CAST (CASE WHEN a.lazy_schema_validation = 0 THEN 'false' ELSE 'true' END as nvarchar) as [lazy schema validation],
    CAST (CASE WHEN a.query_timeout = 0 THEN '0' ELSE a.query_timeout END as nvarchar) as [query timeout],
    CAST (CASE WHEN a.uses_remote_collation = 0 THEN 'false' ELSE 'true' END as nvarchar) as [use remote collation],
    CAST (CASE WHEN a.is_remote_proc_transaction_promotion_enabled = 0 THEN 'false' ELSE 'true' END as nvarchar) as [remote proc transaction promotion]
FROM sys.servers a
WHERE a.server_id > 0
/*조건에 연결된 서버명을 입력하면 해당서버만 추출*/
--AND a.name IN ('')
ORDER BY a.name ASC
FOR READ ONLY
 
OPEN Type_cursor
FETCH FIRST FROM Type_cursor
        INTO @server_id, @name, @product, @provider, @provider_string, @data_source, @catalog, 
            @collation_compatible, @data_access, @dist, @pub, @rpc, @rpc_out, @sub, @connect_timeout,
            @collation_name, @lazy_schema_validation, @query_timeout,
            @use_remote_collation, @remote_proc_transaction_promotion
WHILE (@@FETCH_STATUS = 0)
BEGIN
 
        PRINT '/***** Linked Server ' + @name + ' Script Export *****/'
        PRINT 'USE [master]
GO' + CHAR(10)
        
        PRINT 'IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N''' + @name + ''')'
        PRINT 'EXEC master.dbo.sp_dropserver @server =N'''+ @name + ''', @droplogins=''droplogins''
GO' + CHAR(10)
    IF (@provider_string = 'NULL')
    BEGIN
        IF (@product = 'SQL Server')
        BEGIN
            PRINT 'EXEC master.dbo.sp_addlinkedserver @server = N''' + @name + ''', @srvproduct=N''' + @product + '''' + CHAR(10)
        END
        ELSE IF (@product = '')
        BEGIN
            IF(@catalog= '')
            BEGIN
                PRINT 'EXEC master.dbo.sp_addlinkedserver @server = N''' + @name + ''', @srvproduct=N''' + @product + ''', @provider=N'''+ @provider +''', @datasrc=N''' + @data_source +'''' + CHAR(10)
            END
            ELSE
            BEGIN
                PRINT 'EXEC master.dbo.sp_addlinkedserver @server = N''' + @name + ''', @srvproduct=N''' + @product + ''', @provider=N'''+ @provider +''', @datasrc=N''' + @data_source +''', @catalog=N''' + @catalog+'''' + CHAR(10)
            END
        END
        ELSE
        BEGIN
            IF(@catalog = '')
            BEGIN
                PRINT 'EXEC master.dbo.sp_addlinkedserver @server = N'''+ @name + ''', @srvproduct=N''' + @product + ''', @provider=N'''+ @provider +''', @datasrc=N''' + @data_source +'''' + CHAR(10)
            END
            ELSE
            BEGIN
                PRINT 'EXEC master.dbo.sp_addlinkedserver @server = N'''+ @name + ''', @srvproduct=N''' + @product + ''', @provider=N'''+ @provider +''', @datasrc=N''' + @data_source +''' + @catalog=N''' + @catalog+'''' + CHAR(10)
            END
        END
    END
    ELSE IF (@provider_string IS NOT NULL)
    BEGIN
        IF (@catalog= '')
        BEGIN
            PRINT 'EXEC master.dbo.sp_addlinkedserver @server = N''' + @name + ''', @srvproduct=N''' + @product + ''', @provider=N'''+ @provider +''', @provstr=N''' + @provider_string +'''' + CHAR(10)
        END
        ELSE IF (@catalog IS NOT NULL)
        BEGIN
            PRINT 'EXEC master.dbo.sp_addlinkedserver @server = N''' + @name + ''', @srvproduct=N''' + @product + ''', @provider=N'''+ @provider +''', @provstr=N''' + @provider_string +''', @catalog=N''' + @catalog +'''' + CHAR(10)
        END
    END
 
    DECLARE Type_cursor2 SCROLL CURSOR FOR
    SELECT a.[server_id],
        a.name,
        a.product,
        a.[provider],
        CASE WHEN a.provider_string IS NULL THEN 'NULL' ELSE a.provider_string END as provider_string,
        a.[data_source],
        CAST (CASE WHEN a.catalog IS NULL THEN '' ELSE '' + a.catalog + '' END as nvarchar) as catalog,
        c.name, --as clocallogin,
        CAST (CASE WHEN b.remote_name IS NULL THEN 'NULL' ELSE 'N''' + b.remote_name + '''' END as nvarchar) as rmtuser,
        CAST (CASE WHEN b.uses_self_credential = 0 THEN 'False' ELSE 'True' END as nvarchar) as useself,
        CAST (CASE WHEN b.local_principal_id = 0 THEN 'NULL' ELSE 'N''' + c.name + '''' END as nvarchar) as locallogin
    FROM sys.servers a
    LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id
    LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id
    WHERE a.server_id <> 0 AND a.name = @name
    ORDER BY c.name ASC
    FOR READ ONLY
 
    OPEN Type_cursor2
    FETCH NEXT FROM Type_cursor2
            INTO @server_id, @name, @product, @provider, @provider_string, @data_source, @catalog,
                @clocallogin, @rmtuser, @useself, @locallogin
    WHILE (@@FETCH_STATUS = 0)
        BEGIN
            IF (@useself = 'True') OR (@useself = 'False' AND @locallogin = 'NULL' AND @rmtuser = 'NULL')
                BEGIN
                PRINT 'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N''' + @name + ''', @useself=N''' + @useself + ''', @locallogin='+@locallogin+', @rmtuser=' + @rmtuser+', @rmtpassword=NULL'
                END
            ELSE
                BEGIN
                PRINT 'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N''' + @name + ''', @useself=N''' + @useself + ''', @locallogin='+@locallogin+', @rmtuser=' + @rmtuser+', @rmtpassword=''########'''
                END
 
        FETCH NEXT FROM Type_cursor2
                INTO @server_id, @name, @product, @provider, @provider_string, @data_source, @catalog,
                    @clocallogin, @rmtuser, @useself, @locallogin
        END
    CLOSE Type_cursor2
    DEALLOCATE Type_cursor2
 
    PRINT 'GO' + CHAR(10)
 
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''collation compatible'', @optvalue=N''' + @collation_compatible +'''
GO' + CHAR(10)
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''data access'', @optvalue=N''' + @data_access +'''
GO' + CHAR(10)
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''dist'', @optvalue=N''' + @dist +'''
GO' + CHAR(10)
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''pub'', @optvalue=N''' + @pub +'''
GO' + CHAR(10)
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''rpc'', @optvalue=N''' + @rpc +'''
GO' + CHAR(10)
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''rpc out'', @optvalue=N''' + @rpc_out +'''
GO' + CHAR(10)
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''sub'', @optvalue=N''' + @sub +'''
GO' + CHAR(10)
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''connect timeout'', @optvalue=N''' + @connect_timeout +'''
GO' + CHAR(10)
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''collation name'', @optvalue=' + @collation_name +'
GO' + CHAR(10)
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''lazy schema validation'', @optvalue=N''' + @lazy_schema_validation +'''
GO' + CHAR(10)
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''query timeout'', @optvalue=N''' + @query_timeout +'''
GO' + CHAR(10)
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''use remote collation'', @optvalue=N''' + @use_remote_collation +'''
GO' + CHAR(10)
    PRINT 'EXEC master.dbo.sp_serveroption @server=N''' + @name + ''', @optname=N''remote proc transaction promotion'', @optvalue=N''' + @remote_proc_transaction_promotion +'''
GO' + CHAR(10)
 
FETCH NEXT FROM Type_cursor
        INTO @server_id, @name, @product, @provider, @provider_string, @data_source, @catalog, 
                @collation_compatible, @data_access, @dist, @pub, @rpc, @rpc_out, @sub, @connect_timeout,
                @collation_name, @lazy_schema_validation, @query_timeout,
                @use_remote_collation, @remote_proc_transaction_promotion
END
CLOSE Type_cursor
DEALLOCATE Type_cursor

<출처 : 본인 작성>

 

이상