CREATE PROCEDURE AddAutoBackUP
(
@PlanName varchar(128),--计划名
@Command varchar(8000), --SQL语句
@Freq_Type int=4, --何时将执行作业
@Freq_Interval int=1, --作业执行的天数
@Freq_SubDay_Type int=1, --重复方式
@Freq_SubDay_Interval int=1, --重复周期数
@Freq_Relative_Interval int,
@Freq_Recurrence_Factor int,
@StartDate int,
@StartTime int,
@MaxHistoryLine int,
@Databases varchar(2000),
@OpeType varchar(50)
)
WITH ENCRYPTION
AS
set nocount on
Declare @planID uniqueidentifier
declare @JobID uniqueidentifier --返回作业编号job_id
declare @Enabled int
set @Enabled=1
begin transaction
-------------------------添加计划---------------------------
Execute sp_add_maintenance_plan @plan_name = @PlanName
, @plan_id = @planID OUTPUT
if @@ERROR<>0 goto Abort
-------------------------添加工作---------------------------
execute sp_add_job @job_name = @PlanName
, @enabled = @enabled
, @description = '作业:WeWaySoft 数据库自动备份维护计划'
, @start_step_id = 1
, @category_id = 3
, @job_id = @JobID OUTPUT
if @@ERROR<>0 goto Abort
------------------------创建作业调度---------------------------
Execute sp_add_jobschedule @job_id = @JobID
, @name = '第 1 调度'
, @enabled = @enabled
, @freq_type = @Freq_Type
, @freq_interval = @Freq_Interval
, @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 = @StartDate
, @active_end_date = 99991231
, @active_start_time = @StartTime
, @active_end_time = 235959
if @@ERROR<>0 goto Abort
---------------------------添加服务器-------------------------
Execute sp_add_jobserver @job_id=@JobID
if @@ERROR<>0 goto Abort
----------------------------添加步骤------------------------
if(charindex('2000',left(@@VERSION,26))>0)set @Command=replace(@Command,'(plan_id)',@planID)
Execute sp_add_jobstep @job_id = @JobID
, @step_id = 1
, @step_name = '第 1 步'
, @subsystem = 'TSQL'
, @command = @Command
-- @on_success_action = 1--成功后退出
-- @on_success_step_id = 0
-- @on_fail_action = 2 --失败后退出
-- @on_fail_step_id = 0
, @database_name = 'master'
-- @output_file_name = NULL
, @flags = 4
if @@ERROR<>0 goto Abort
---------------------------添加用户标示---------------------------
--'AutoBackup'
update sysdbmaintplans set max_history_rows=@MaxHistoryLine, user_defined_1=1, user_defined_2=@OpeType where plan_id=@planID
if @@ERROR<>0 goto Abort
-----------------------将数据库和维护计划相关联--------------------
declare @subDatabase varchar(100)
declare @intPoint int
set @intPoint=charindex( ',',@Databases)
while @intPoint>0
begin
set @subDatabase=rtrim(ltrim(substring(@Databases,1,@intPoint-1)))
set @Databases=substring(@Databases,@intPoint+1,len(@Databases))
if @subDatabase<>''
begin
execute sp_add_maintenance_plan_db @plan_id = @planID,@db_name = @subDatabase
if @@ERROR<>0 goto Abort
end
set @intPoint=charindex( ',',@Databases)
end
if @Databases<>''
begin
execute sp_add_maintenance_plan_db @plan_id = @planID,@db_name = @Databases
if @@ERROR<>0 goto Abort
end
---------------------将维护计划和现有的作业相关联-------------------------
Execute InsertInfoJobAndPlan @plan_id = @planID
, @job_id = @JobID
if @@ERROR<>0 goto Abort
Complete:
commit transaction
select @planID as GUID,@Enabled as Enabled
return 0
Abort:
rollback transaction
GO