亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

SQL Server 作業同步

系統 2268 0

昨天發了篇 SQL Server 作業備份? http://www.cnblogs.com/Amaranthus/archive/2012/06/25/2561569.html

今天就加上powershell 把 作業同步 完善起來

核心導出作業的 代碼 和 作業備份是相似的

      
        alter
      
      
        PROC
      
       DumpJob (
      
        @job
      
      
        VARCHAR
      
      (
      
        100
      
      
        ))


      
      
        AS
      
      
        DECLARE
      
      
        @retrun
      
      
        NVARCHAR
      
      (
      
        max
      
      
        )


      
      
        DECLARE
      
      
        @jobname
      
      
        VARCHAR
      
      (
      
        30
      
      ),
      
        @category_calss_i
      
      
        INT
      
       ,
      
        @category_calss
      
      
        VARCHAR
      
      (
      
        50
      
      ),
      
        @category_name
      
      
        VARCHAR
      
      (
      
        50
      
      
        )

,
      
      
        @category_type
      
      
        VARCHAR
      
      (
      
        30
      
      ),
      
        @category_id
      
      
        int
      
      
        

,
      
      
        @category_type_i
      
      
        int
      
      
        SELECT
      
      
        @jobname
      
      
        =
      
      
        '
      
      
        powershell
      
      
        '
      
      ,
      
        @category_calss
      
      
        =
      
      
        ''
      
      ,
      
        @category_name
      
      
        =
      
      
        ''
      
      ,
      
        @category_type
      
      
        =
      
      
        ''
      
      
        SELECT
      
      
        @jobname
      
      
        =
      
      
        @job
      
      
        SELECT
      
      
        @category_calss
      
      
        =
      
      
        CASE
      
      
        WHEN
      
       tshc.category_class 
      
        =
      
      
        1
      
      
        THEN
      
      
        '
      
      
        JOB
      
      
        '
      
      
        WHEN
      
       tshc.category_class 
      
        =
      
      
        2
      
      
        THEN
      
      
        '
      
      
        ALERT
      
      
        '
      
      
        else
      
      
        '
      
      
        OPERATOR
      
      
        '
      
      
        END
      
      
           

, 
      
      
        @category_type
      
      
        =
      
      
        CASE
      
      
        WHEN
      
       tshc.category_type 
      
        =
      
      
        1
      
      
        THEN
      
      
        '
      
      
        LOCAL
      
      
        '
      
      
        WHEN
      
       tshc.category_type 
      
        =
      
      
        2
      
      
        THEN
      
      
        '
      
      
        MULTI-SERVER
      
      
        '
      
      
        else
      
      
        '
      
      
        NONE
      
      
        '
      
      
        END
      
      
          

,
      
      
        @category_name
      
      
        =
      
      
         tshc.name

,
      
      
        @category_type_i
      
      
        =
      
      
         category_type

,
      
      
        @category_calss_i
      
      
        =
      
      
         tshc.category_class

,
      
      
        @category_id
      
      
        =
      
      
         tshc.category_id


      
      
        FROM
      
      
        

msdb.dbo.sysjobs_view 
      
      
        AS
      
      
         sv


      
      
        INNER
      
      
        JOIN
      
       msdb.dbo.syscategories  
      
        AS
      
       tshc 
      
        ON
      
       sv.category_id 
      
        =
      
      
         tshc.category_id


      
      
        WHERE
      
      
        

(sv.name
      
      
        =
      
      
        @jobname
      
      
        AND
      
       tshc.category_class 
      
        =
      
      
        1
      
      
        )




      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        '
      
      
         BEGIN TRANSACTION
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        DECLARE @ReturnCode INT
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N
      
      
        '''
      
      
        +
      
      
        @category_name
      
      
        +
      
      
        '''
      
      
        AND category_class=
      
      
        '
      
      
        +
      
      
        rtrim
      
      (
      
        @category_calss_i
      
      )
      
        +
      
      
        '
      
      
        )
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        BEGIN
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N
      
      
        '''
      
      
        +
      
      
        @category_calss
      
      
        +
      
      
        '''
      
      
        , @type=N
      
      
        '''
      
      
        +
      
      
        @category_type
      
      
        +
      
      
        '''
      
      
        , @name=N
      
      
        '''
      
      
        +
      
      
        @category_name
      
      
        +
      
      
        ''''
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        end
      
      
        '
      
      
        DECLARE
      
      
        @EventLogLevel
      
      
        INT
      
      ,
      
        @EmailLevel
      
      
        INT
      
      ,
      
        @NetSendLevel
      
      
        INT
      
      ,
      
        @PageLevel
      
      
        INT
      
      
        DECLARE
      
      
        @EmailLeveloprid
      
      
        NVARCHAR
      
      (
      
        256
      
      ),
      
        @NetSendLeveloprid
      
      
        NVARCHAR
      
      (
      
        256
      
      ),
      
        @PageLeveloprid
      
      
        NVARCHAR
      
      (
      
        256
      
      
        )


      
      
        DECLARE
      
      
        @isenable
      
      
        INT
      
       , 
      
        @description
      
      
        NVARCHAR
      
      (
      
        1024
      
      ),
      
        @owner_log_name
      
      
        Nvarchar
      
      (
      
        512
      
      ),
      
        @delete_level
      
      
        INT
      
      
        DECLARE
      
      
        @jobId
      
      
        UNIQUEIDENTIFIER
      
      ,
      
        @start_step_id
      
      
        INT
      
      ,
      
        @server
      
      
        NVARCHAR
      
      (
      
        512
      
      
        )


      
      
        SELECT
      
      
        @EventLogLevel
      
      
        =
      
      
        sv.notify_level_eventlog 

,
      
      
        @EmailLevel
      
      
        =
      
      
        sv.notify_level_email  

,
      
      
        @NetSendLevel
      
      
        =
      
      
        sv.notify_level_netsend  

,
      
      
        @PageLevel
      
      
        =
      
      
        sv.notify_level_page  

,
      
      
        @EmailLeveloprid
      
      
        =
      
      
        ISNULL
      
      ((
      
        SELECT
      
      
        TOP
      
      
        1
      
       name 
      
        FROM
      
         msdb..sysoperators 
      
        WHERE
      
       id 
      
        =
      
       sv.notify_email_operator_id),
      
        ''
      
      
        )

,
      
      
        @NetSendLeveloprid
      
      
        =
      
      
        ISNULL
      
      ((
      
        SELECT
      
      
        TOP
      
      
        1
      
       name 
      
        FROM
      
         msdb..sysoperators 
      
        WHERE
      
       id 
      
        =
      
       sv.notify_netsend_operator_id),
      
        ''
      
      
        )

,
      
      
        @PageLeveloprid
      
      
        =
      
      
        ISNULL
      
      ((
      
        SELECT
      
      
        TOP
      
      
        1
      
       name 
      
        FROM
      
         msdb..sysoperators 
      
        WHERE
      
       id 
      
        =
      
       sv.notify_page_operator_id),
      
        ''
      
      
        )

,
      
      
        @isenable
      
      
        =
      
      
         sv.enabled

,
      
      
        @description
      
      
        =
      
      
         sv.description

,
      
      
        @owner_log_name
      
      
        =
      
      
        ISNULL
      
      (
      
        suser_sname
      
      (sv.owner_sid), N
      
        ''''
      
      
        )  

,
      
      
        @delete_level
      
      
        =
      
      
         sv.delete_level

,
      
      
        @jobId
      
      
        =
      
      
         sv.job_id

,
      
      
        @start_step_id
      
      
        =
      
      
         start_step_id

,
      
      
        @server
      
      
        =
      
      
         originating_server


      
      
        FROM
      
       msdb.dbo.sysjobs_view 
      
        AS
      
      
         sv


      
      
        WHERE
      
       (sv.name
      
        =
      
      
        @jobname
      
      
        and
      
       sv.category_id
      
        =
      
      
        0
      
      
        )






      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        DECLARE @jobId BINARY(16)
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N
      
      
        '''
      
      
        +
      
      
        @jobname
      
      
        +
      
      
        '''
      
      
        ,
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @enabled=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @isenable
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @notify_level_eventlog=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @EventLogLevel
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @notify_level_email=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @EmailLevel
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @notify_level_netsend=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @NetSendLevel
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @notify_level_page=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @PageLevel
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @notify_email_operator_name =
      
      
        '''
      
      
        +
      
      
        RTRIM
      
      (
      
        @EmailLeveloprid
      
      )
      
        +
      
      
        '''
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @notify_netsend_operator_name=
      
      
        '''
      
      
        +
      
      
        RTRIM
      
      (
      
        @NetSendLeveloprid
      
      )
      
        +
      
      
        '''
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @notify_page_operator_name=
      
      
        '''
      
      
        +
      
      
        RTRIM
      
      (
      
        @PageLeveloprid
      
      )
      
        +
      
      
        '''
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @delete_level=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @delete_level
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @description=N
      
      
        '''
      
      
        +
      
      
        @description
      
      
        +
      
      
        '''
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @category_name=N
      
      
        '''
      
      
        +
      
      
        @category_name
      
      
        +
      
      
        '''
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @owner_login_name=N
      
      
        '''
      
      
        +
      
      
        @owner_log_name
      
      
        +
      
      
        '''
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @job_id = @jobId OUTPUT
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
      
      
        '
      
      
        --
      
      
        SELECT * FROM msdb.dbo.syscategories 
      
      
        DECLARE
      
      
        @step_id
      
      
        INT
      
      
        declare
      
      
        @step_name
      
      
        nvarchar
      
      (
      
        512
      
      ) ,
      
        @cmdexec_success_code
      
      
        INT
      
      ,
      
        @on_success_action
      
      
        INT
      
      ,
      
        @on_success_step_id
      
      
        INT
      
      
        

        ,
      
      
        @on_fail_action
      
      
        INT
      
      ,
      
        @on_fail_step_id
      
      
        INT
      
      ,
      
        @retry_attempts
      
      
        INT
      
      ,
      
        @retry_interval
      
      
        INT
      
      ,
      
        @os_run_priority
      
      
        INT
      
      
        

        ,
      
      
        @subsystem
      
      
        NVARCHAR
      
      (
      
        512
      
      ),
      
        @database_name
      
      
        NVARCHAR
      
      (
      
        512
      
      ),
      
        @flags
      
      
        INT
      
      ,
      
        @command
      
      
        NVARCHAR
      
      (
      
        max
      
      
        )




      
      
        DECLARE
      
       jbcur 
      
        CURSOR
      
      
        FOR
      
      
        SELECT
      
       step_id  
      
        FROM
      
       msdb..sysjobsteps   
      
        WHERE
      
       job_id 
      
        =
      
      
        @jobid
      
      
        ORDER
      
      
        BY
      
      
         step_id ;


      
      
        OPEN
      
      
         jbcur;


      
      
        FETCH
      
      
        NEXT
      
      
        FROM
      
       jbcur 
      
        INTO
      
      
        @step_id
      
      
        WHILE
      
      
        @@FETCH_STATUS
      
      
        =
      
      
        0
      
      
        BEGIN
      
      
        SELECT
      
      
        @step_name
      
      
        =
      
      
         step_name

            ,
      
      
        @cmdexec_success_code
      
      
        =
      
      
         cmdexec_success_code

            ,
      
      
        @on_success_action
      
      
        =
      
      
         on_success_action

            ,
      
      
        @on_success_step_id
      
      
        =
      
      
         on_success_step_id

            ,
      
      
        @on_fail_action
      
      
        =
      
      
         on_fail_action

            ,
      
      
        @on_fail_step_id
      
      
        =
      
      
         on_fail_step_id

            ,
      
      
        @retry_attempts
      
      
        =
      
      
         retry_attempts

            ,
      
      
        @retry_interval
      
      
        =
      
      
         retry_interval

            ,
      
      
        @os_run_priority
      
      
        =
      
      
         os_run_priority

            ,
      
      
        @subsystem
      
      
        =
      
      
         subsystem

            ,
      
      
        @database_name
      
      
        =
      
      
         database_name

            ,
      
      
        @command
      
      
        =
      
      
         command

            ,
      
      
        @flags
      
      
        =
      
      
         flags

    
      
      
        FROM
      
        msdb..sysjobsteps a 
      
        WHERE
      
       job_id 
      
        =
      
      
        @jobid
      
      
        and
      
       step_id  
      
        =
      
      
        @step_id
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @step_name=N
      
      
        '''
      
      
        +
      
      
        @step_name
      
      
        +
      
      
        '''
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @step_id=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @step_id
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @cmdexec_success_code=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @cmdexec_success_code
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @on_success_action=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @on_success_action
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @on_success_step_id=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @on_success_step_id
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @on_fail_action=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @on_fail_action
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @on_fail_step_id=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @on_fail_step_id
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @retry_attempts=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @retry_attempts
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @retry_interval=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @retry_interval
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @os_run_priority=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @os_run_priority
      
      )
      
        +
      
      
        '
      
      
        , @subsystem=N
      
      
        '''
      
      
        +
      
      
        @subsystem
      
      
        +
      
      
        '''
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @database_name=N
      
      
        '''
      
      
        +
      
      
        @database_name
      
      
        +
      
      
        '''
      
      
        ,
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @flags=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @flags
      
      )
      
        +
      
      
        '
      
      
         ,
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         @command=N
      
      
        '''
      
      
        +
      
      
        REPLACE
      
      (
      
        @command
      
      ,
      
        ''''
      
      ,
      
        ''''''
      
      )
      
        +
      
      
        ''''
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
      
      
        '
      
      
        FETCH
      
      
        NEXT
      
      
        FROM
      
       jbcur 
      
        INTO
      
      
        @step_id
      
      
        END
      
      
        CLOSE
      
      
         jbcur


      
      
        DEALLOCATE
      
      
         jbcur

    


      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 
      
      
        '
      
      
        +
      
      
        rtrim
      
      (
      
        @start_step_id
      
      
        )


      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback    
      
      
        '
      
      
        DECLARE
      
      
        @enabled
      
      
        INT
      
      ,
      
        @freq_type
      
      
        INT
      
      ,
      
        @freq_interval
      
      
        INT
      
      ,
      
        @freq_subday_type
      
      
        INT
      
      ,
      
        @freq_subday_interval
      
      
        INT
      
      
        

    ,
      
      
        @freq_relative_interval
      
      
        INT
      
      ,
      
        @freq_recurrence_factor
      
      
        INT
      
      ,
      
        @active_start_date
      
      
        INT
      
      ,
      
        @active_end_date
      
      
        INT
      
      
        

    ,
      
      
        @active_start_time
      
      
        INT
      
      ,
      
        @active_end_time
      
      
        INT
      
      ,
      
        @name
      
      
        VARCHAR
      
      (
      
        512
      
      
        )




      
      
        SELECT
      
      
        @name
      
      
        =
      
      
         a.name

,
      
      
        @enabled
      
      
        =
      
      
         enabled

,
      
      
        @freq_interval
      
      
        =
      
      
         freq_interval

,
      
      
        @freq_type
      
      
        =
      
      
         freq_type

,
      
      
        @freq_subday_type
      
      
        =
      
      
        freq_subday_type

,
      
      
        @freq_subday_interval
      
      
        =
      
      
        freq_subday_interval

,
      
      
        @freq_relative_interval
      
      
        =
      
      
        freq_relative_interval

,
      
      
        @freq_recurrence_factor
      
      
        =
      
      
        freq_recurrence_factor

,
      
      
        @active_start_date
      
      
        =
      
      
        active_start_date

,
      
      
        @active_end_date
      
      
        =
      
      
        active_end_date

,
      
      
        @active_start_time
      
      
        =
      
      
        active_start_time

,
      
      
        @active_end_time
      
      
        =
      
      
        active_end_time

 
      
      
        FROM
      
      
         msdb..sysschedules a

    
      
      
        INNER
      
      
        JOIN
      
       msdb.dbo.sysjobschedules b 
      
        ON
      
       a.schedule_id 
      
        =
      
      
         b.schedule_id


      
      
        WHERE
      
        job_id 
      
        =
      
      
        @jobId
      
      
        IF
      
      (
      
        @name
      
      
        IS
      
      
        not
      
      
        null
      
      
        )


      
      
        begin
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N
      
      
        '''
      
      
        +
      
      
        @name
      
      
        +
      
      
        '''
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            @enabled=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @enabled
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            @freq_type=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @freq_type
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            @freq_interval=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @freq_interval
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            @freq_subday_type=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @freq_subday_type
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            @freq_subday_interval=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @freq_subday_interval
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            @freq_relative_interval=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @freq_relative_interval
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            @freq_recurrence_factor=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @freq_recurrence_factor
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            @active_start_date=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @active_start_date
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            @active_end_date=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @active_end_date
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            @active_start_time=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @active_start_time
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            @active_end_time=
      
      
        '
      
      
        +
      
      
        RTRIM
      
      (
      
        @active_end_time
      
      )
      
        +
      
      
        '
      
      
        , 
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            @schedule_uid=N
      
      
        '''
      
      
        +
      
      
        RTRIM
      
      (
      
        NEWID
      
      ())
      
        +
      
      
        ''''
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
      
      
        '
      
      
        END
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N
      
      
        ''
      
      
        (local)
      
      
        '''
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
         IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        COMMIT TRANSACTION
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        GOTO EndSave
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        QuitWithRollback:
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
            IF(@@TRANCOUNT>0)ROLLBACK TRANSACTION
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        EndSave:
      
      
        '
      
      
        SET
      
      
        @retrun
      
      
        =
      
      
        @retrun
      
      
        +
      
      
        CHAR
      
      (
      
        13
      
      )
      
        +
      
      
        CHAR
      
      (
      
        10
      
      ) 
      
        +
      
      
        '
      
      
        '
      
      
        select
      
      
        @retrun
      
    

?

我創建了一個存儲過程,用來導出 作業,只有用powershell 腳本來實現同步,你可以powershell 腳本放入 sqlagent 中 定時運行起到同步的效果

一下是powershell 代碼:

      
        $server
      
       = 
      
        "
      
      
        (local)
      
      
        "
      
      
        $uid
      
       = 
      
        "
      
      
        sa
      
      
        "
      
      
        $db
      
      =
      
        "
      
      
        master
      
      
        "
      
      
        $pwd
      
      =
      
        "
      
      
        fanzhouqi
      
      
        "
      
      
        $mailprfname
      
       = 
      
        "
      
      
        sina
      
      
        "
      
      
        $recipients
      
       = 
      
        "
      
      
        32116057@qq.com
      
      
        "
      
      
        $subject
      
       =
      
         'System Log'


      
      
        function
      
       execproc(
      
        $message
      
      
        )

{

    
      
      
        $SqlConnection
      
       = New-
      
        Object System.Data.SqlClient.SqlConnection 

    
      
      
        $CnnString
      
       =
      
        "
      
      
        Server = $server; Database = $db;User Id = $uid; Password = $pwd
      
      
        "
      
      
        $SqlConnection
      
      .ConnectionString = 
      
        $CnnString
      
      
        $CC
      
       = 
      
        $SqlConnection
      
      
        .CreateCommand(); 

    

    
      
      
        $CC
      
      .CommandText=
      
        $message
      
      
        $adapter
      
       = New-Object  System.Data.SqlClient.SqlDataAdapter 
      
        $CC
      
      
        $dataset
      
       = New-
      
        Object System.Data.DataSet

    
      
      
        #
      
      
        $SqlConnection.SelectCommand = $CC
      
      
        if
      
       (
      
        -not
      
       (
      
        $SqlConnection
      
      .State 
      
        -like
      
      
        "
      
      
        Open
      
      
        "
      
      )) { 
      
        $SqlConnection
      
      
        .Open() } 

    

    
      
      
        $adapter
      
      .Fill(
      
        $dataset
      
      ) |out-
      
        null

    
      
      
        $dataset
      
      .Tables[0].Rows[0][0
      
        ]

    
      
      
        $SqlConnection
      
      
        .Close();

}


      
      
        function
      
       execsql(
      
        $message
      
      
        )

{

    
      
      
        $SqlConnection
      
       = New-
      
        Object System.Data.SqlClient.SqlConnection 

    
      
      
        $CnnString
      
       =
      
        "
      
      
        Server = fanr-pc\sql2012; Database = $db;User Id = $uid; Password = $pwd
      
      
        "
      
      
        $SqlConnection
      
      .ConnectionString = 
      
        $CnnString
      
      
        $CC
      
       = 
      
        $SqlConnection
      
      
        .CreateCommand(); 

    
      
      
        if
      
       (
      
        -not
      
       (
      
        $SqlConnection
      
      .State 
      
        -like
      
      
        "
      
      
        Open
      
      
        "
      
      )) { 
      
        $SqlConnection
      
      
        .Open() } 

    

    
      
      
        $cc
      
      .CommandText=
      
        $message
      
      
        $cc
      
      .ExecuteNonQuery()|out-
      
        null 

    
      
      
        $SqlConnection
      
      
        .Close();

}


      
      
        $jobscript
      
       =  execproc 
      
        "
      
      
         EXEC master..DumpJob @job = 'backup'
      
      
        "
      
      
        #
      
      
        $jobscript 
      
      

execsql 
      
        $jobscript
      
    

有什么問題可以聯系我:如果blog 的代碼沒辦法使用也可以 加我qq 聯系我,問我要。qq:32116057 fanr

SQL Server 作業同步


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 曰本还a大片免费无播放器 曰本黄色录像 | 国产成人亚洲综合一区 | 成人精品视频在线观看播放 | 国产香蕉精品视频 | 91在线 | 欧美: | 久久99热精品免费观看欧美 | 久久国产这里只精品免费 | 日韩欧美亚洲每日更新网 | 国产精品免费看香蕉 | yellow中文字幕久久网 | 午夜视频久久久久一区 | 亚洲色欧美 | 高清国产美女在线观看 | 日本xxxxxbbbbb精品 | 九九这里只有精品 | 国产精品久久久一区二区三区 | 最刺激黄a大片免费观看下截 | 青青青国产精品一区二区 | 九九视频在线 | 亚洲精品一区二区不卡 | 日本b站一卡二不卡 | 久久美剧| 午夜狠狠操 | 黄页网址在线免费观看 | 亚洲精品国产精品乱码视色 | 中文字暮文字暮 | 日韩中文字幕在线 | 97视频免费 | 国产精品免费大片 | 天天操天天操天天操 | 黄色片一级毛片 | 97在线观看免费 | 亚洲精品久久99久久 | 国产精品日韩欧美一区二区 | 国产真实乱子伦精品视 | 国产精品每日更新在线观看 | 久久精品只有这里有 | 男人天堂视频在线观看 | 婷婷的久久五月综合先锋影音 | 精品毛片免费看 | 日本高清视频一区二区三区 |