批量导出当前实例下的所有linkedserver代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】批量导出当前实例下的所有linkedserver
作者:lyhabc / 发布于2015/2/28/ 1052
很多时候，我们都需要导出实例下面的登录用户，job，linkedserver等等导出job比较复杂，下午写了一个脚本把所有的linkedserver导出来，但是密码不会显示出来下面脚本在SQL2008 R2下面测试通过

	-- =============================================
	-- Author: <桦仔>
	-- Blog: <http://www.cnblogs.com/lyhabc/>
	-- Create date: <2014/11/3>
	-- Description: <批量导出实例下所有linkedserver>
	-- LINK: http://msdn.microsoft.com/zh-cn/library/ms189811.aspx
	-- =============================================
	SET NOCOUNT ON
	USE [master]
	GO
	DECLARE @servername NVARCHAR(2000)
	DECLARE @id INT
	DECLARE @scriptdate NVARCHAR(200)
	DECLARE @productName NVARCHAR(2000)
	DECLARE @datasource NVARCHAR(4000)
	DECLARE @useself BIT
	DECLARE @dist BIT
	DECLARE @remoteuser NVARCHAR(2000)
	DECLARE @collationcompatible BIT
	DECLARE @dataaccess BIT
	DECLARE @sub BIT
	DECLARE @pub BIT
	DECLARE @rpc BIT
	DECLARE @rpcout BIT
	DECLARE @connecttimeout BIGINT
	DECLARE @lazyschemavalidation BIT
	DECLARE @querytimeout BIGINT
	DECLARE @useremotecollation BIT
	DECLARE @remoteproctransactionpromotion BIT

	

	DECLARE LinkserverNameCur CURSOR
	FOR
	 SELECT srv.name AS [Name] ,
	 CAST(srv.server_id AS INT) AS [ID]
	 FROM sys.servers AS srv
	 WHERE ( srv.server_id != 0 )

	OPEN LinkserverNameCur
	FETCH NEXT FROM LinkserverNameCur INTO @servername, @id
	WHILE @@FETCH_STATUS = 0
	 BEGIN
	 SELECT @servername = srv.name ,
	 @datasource = ISNULL(srv.data_source, N'''') ,
	 @productName = srv.product ,
	 @collationcompatible = CAST(srv.is_collation_compatible AS BIT) ,
	 @dataaccess = CAST(srv.is_data_access_enabled AS BIT) ,
	 @dist = CAST(srv.is_distributor AS BIT) ,
	 @pub = CAST(srv.is_publisher AS BIT) ,
	 @rpc = CAST(srv.is_remote_login_enabled AS BIT) ,
	 @rpcout = CAST(srv.is_rpc_out_enabled AS BIT) ,
	 @sub = CAST(srv.is_subscriber AS BIT) ,
	 @connecttimeout = srv.connect_timeout ,
	 @lazyschemavalidation = srv.lazy_schema_validation ,
	 @querytimeout = srv.query_timeout ,
	 @useremotecollation = srv.uses_remote_collation ,
	 @remoteproctransactionpromotion = CAST(srv.is_remote_proc_transaction_promotion_enabled AS BIT)
	FROM sys.servers AS srv
	WHERE ( srv.server_id != 0 )
	 AND ( srv.name = @servername )
	 AND ( srv.[server_id] = @id )

	SELECT @remoteuser = ISNULL(ll.remote_name, N'') ,
	 @useself = CAST(ll.uses_self_credential AS BIT)
	FROM sys.servers AS srv
	 INNER JOIN sys.linked_logins ll ON ll.server_id = CAST(srv.server_id AS INT)
	 LEFT OUTER JOIN sys.server_principals sp ON ll.local_principal_id = sp.principal_id
	WHERE ( ( srv.server_id != 0 )
	 AND ( srv.name = @servername)
	 )

	 IF (@servername IS NOT NULL AND @id IS NOT NULL)
	 BEGIN
	 SELECT @scriptdate=CONVERT(NVARCHAR(200),GETDATE(),120)
	 PRINT '/*************************************SCRIPT FOR LINKED SERVER: ['+@servername+']****************************************************/'
	 PRINT '/********************************************************************************************************************************/'
	 PRINT 'USE [master]'+CHAR(13)+'GO'
	 PRINT '/****** Object: LinkedServer ['+@servername+'] Script Date: '+@scriptdate+' ******/'
	 PRINT 'EXEC master.dbo.sp_addlinkedserver @server = N'''+@servername+''', @srvproduct=N'''+@productName+''''
	 PRINT '/* For security reasons the linked server remote logins password is changed with ######## */'

	 DECLARE @sql NVARCHAR(2000)
	 IF (@remoteuser IS NOT NULL AND @remoteuser != N'')
	 BEGIN
	 SET @sql='EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'''+@datasource+''',@useself=N'''+CASE @useself WHEN 0 THEN 'false' ELSE 'true' END +''',@locallogin=NULL,@rmtuser=N'''+@remoteuser+''',@rmtpassword=''########'''

	 END
	 ELSE
	 BEGIN
	 SET @sql='EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'''+@datasource+''',@useself=N'''+CASE @useself WHEN 0 THEN 'false' ELSE 'true' END +''',@locallogin=NULL,@rmtuser=N'''',@rmtpassword=''########'''

	 END
	 PRINT @sql
	 PRINT 'GO'

	 PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''collation compatible'', @optvalue=N'''+CASE @useself WHEN 0 THEN 'false' ELSE 'true' END+''''
	 PRINT 'GO'

	 PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''data access'', @optvalue=N'''+CASE @dataaccess WHEN 0 THEN 'false' ELSE 'true' END+''''
	 PRINT 'GO'
	 PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''dist'', @optvalue=N'''+CASE @dist WHEN 0 THEN 'false' ELSE 'true' END+''''
	 PRINT 'GO'
	 PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''pub'', @optvalue=N'''+CASE @pub WHEN 0 THEN 'false' ELSE 'true' END+''''
	 PRINT 'GO'
	 PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''rpc'', @optvalue=N'''+CASE @rpc WHEN 0 THEN 'false' ELSE 'true' END+''''
	 PRINT 'GO'
	 PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''rpc out'', @optvalue=N'''+CASE @rpcout WHEN 0 THEN 'false' ELSE 'true' END+''''
	 PRINT 'GO'
	 PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''sub'', @optvalue=N'''+CASE @sub WHEN 0 THEN 'false' ELSE 'true' END+''''
	 PRINT 'GO'
	 PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''connect timeout'', @optvalue=N'''+CAST(@connecttimeout AS NVARCHAR(200))+''''
	 PRINT 'GO'
	 PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''lazy schema validation'', @optvalue=N'''+CASE @lazyschemavalidation WHEN 0 THEN 'false' ELSE 'true' END+''''
	 PRINT 'GO'
	 PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''query timeout'', @optvalue=N'''+CAST(@querytimeout AS NVARCHAR(200))+''''
	 PRINT 'GO'
	 PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''use remote collation'', @optvalue=N'''+CASE @useremotecollation WHEN 0 THEN 'false' ELSE 'true' END+''''
	 PRINT 'GO'
	 PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''remote proc transaction promotion'', @optvalue=N'''+CASE @remoteproctransactionpromotion WHEN 0 THEN 'false' ELSE 'true' END+''''
	 PRINT 'GO'

	 PRINT '/********************************************************************************************************************************/'
	 PRINT '/********************************************************************************************************************************/'
	 PRINT CHAR(13)
	 PRINT CHAR(13)
	 PRINT CHAR(13)

	 END

	
	 FETCH NEXT FROM LinkserverNameCur INTO @servername, @id
	 END
	CLOSE LinkserverNameCur
	DEALLOCATE LinkserverNameCur
试试其它关键字
　linkedserver　
同语言下
可能有用的
lyhabc贡献的其它代码(4)