导出SQL Server中所有Job的最简单方法；批量导出LinkServer脚本；

应用场景：

在将源SQL Server数据库服务器中的所有Job（作业）迁移至目标数据库服务器的过程中，需要先将这些Job导出为SQL脚本。

操作步骤：

1、在Microsoft SQL Server Management Studio中，选择SQL Server Agent->Jobs，按F7打开Object Explorer Details窗口，选中所有Job；

2、点击鼠标右键，选择Script Job as->CREATE To->New Query Editor Windows，然后所有Job会以SQL脚本的方式显示在查询窗口中。

批量导出LinkServer脚本：

    -- =============================================
-- 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