SQL Server/스크립트
[MSSQL] 연결된서버(LinkedServer) 스크립트 추출
マサキ
2023. 9. 18. 14:08
SQL Server에서 GUI를 바탕으로 연결된서버 Export스크립트화가 가능하지만
스크립트로 Export하는 쿼리를 작성해보았다.
(패스워드는 복호화 불가능함)
테스트는 SQL Server 2012, 2019에서 실행
SQL Server 2008 R2이상 버전에서 사용 가능
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
|
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
|
cs |
<출처 : 본인 작성>
이상