分享一个批量导出当前实例下的所有linkedserver脚本

很多时候，我们都需要导出实例下面的登录用户，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 CURSORFOR    SELECT  srv.name AS [Name] ,            CAST(srv.server_id AS INT) AS [ID]    FROM    sys.servers AS srv    WHERE   ( srv.server_id != 0 )OPEN LinkserverNameCurFETCH NEXT FROM LinkserverNameCur INTO @servername, @idWHILE @@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    ENDCLOSE LinkserverNameCurDEALLOCATE LinkserverNameCur

如果要迁移登录用户这里有一篇文章

如何在 SQL Server 2005 实例之间传输登录和密码

脚本如下:

USE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL  DROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal    @binvalue varbinary(256),    @hexvalue varchar (514) OUTPUTASDECLARE @charvalue varchar (514)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @length = DATALENGTH (@binvalue)SELECT @hexstring = '0123456789ABCDEF'WHILE (@i <= @length)BEGIN  DECLARE @tempint int  DECLARE @firstint int  DECLARE @secondint int  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))  SELECT @firstint = FLOOR(@tempint/16)  SELECT @secondint = @tempint - (@firstint*16)  SELECT @charvalue = @charvalue +    SUBSTRING(@hexstring, @firstint+1, 1) +    SUBSTRING(@hexstring, @secondint+1, 1)  SELECT @i = @i + 1ENDSELECT @hexvalue = @charvalueGO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL  DROP PROCEDURE sp_help_revloginGOCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL ASDECLARE @name sysnameDECLARE @type varchar (1)DECLARE @hasaccess intDECLARE @denylogin intDECLARE @is_disabled intDECLARE @PWD_varbinary  varbinary (256)DECLARE @PWD_stri