createbackupdbjobproc.sql

对于sql server2005数据库中

-- 2.创建存储过程CreateAutoDelJobProc
use dbas52
IF EXISTS (SELECT name FROM sysobjects WHERE  name = N'CreateBackupDBJobProc' AND type = 'P')
   DROP PROCEDURE CreateBackupDBJobProc
GO
CREATE PROCEDURE CreateBackupDBJobProc
    @ServerName nvarchar(50),
    @DataBaseName nvarchar(50),
    @FolderPath nvarchar(1000),
    @JobFreq int,
	@StartTime nvarchar(20),
	@IsEnable bit
AS
BEGIN TRANSACTION  
declare @JobId binary(16)
declare @ReturnCode int
declare @ProcCmd nvarchar(200)

--默认参数
declare @CategoryName nvarchar(50)
declare @JobName	nvarchar(50)
declare @ProcName	nvarchar(50)
declare @StepName	nvarchar(50)
declare @ScheduleName nvarchar(50)
declare @FreqInterval int
declare @FreqFactor int
declare @ErrorName nvarchar(50)

set @CategoryName = N'BackupDBCategory'
set @JobName = N'BackupDBJob'
set @ProcName = N'.dbo.BackupDBProc'
set @StepName = N'BackupDBProcStep'
set @ScheduleName = N'BackupDBJobSchedule'
set @FreqInterval = 1 --周日或月初
set @FreqFactor = 1 -- 周或月备份时 备份时间之间的间隔
set @ErrorName = N'无法导入作业' + @JobName + N',因为已经有相同名称的多重服务器作业.'

-- 创建作业类别
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = @CategoryName) < 1 
BEGIN
	EXECUTE @ReturnCode = msdb.dbo.sp_add_category @name = @CategoryName
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

SELECT @JobId = job_id FROM msdb.dbo.sysjobs WHERE (name = @JobName)
IF (@JobID IS NOT NULL)    
BEGIN  
-- 检查此作业是否为多重服务器作业  
IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) 
-- 已经存在，因而终止脚本 
BEGIN
  RAISERROR (@ErrorName, 16, 1) 
  GOTO QuitWithRollback
END
ELSE 
BEGIN
  -- 删除［本地］作业 
  EXECUTE @ReturnCode = msdb.dbo.sp_delete_job @job_id = @JobId
  SET @JobId = NULL
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
END 

-- 添加作业
print @ReturnCode
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobId OUTPUT, @job_name = @JobName, @category_name = @CategoryName
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- 添加作业步骤
set @ProcCmd = N'EXECUTE ' + @DataBaseName + '.dbo.BackupDBProc ''' + @DataBaseName + N''',''' + @FolderPath + ''''
print @ProcCmd
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobId, @step_id = 1, @step_name=@StepName, @command= @ProcCmd
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- 更新作业属性，从step1开始
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobId, @start_step_id = 1 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- 添加作业调度
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobId, @name = @ScheduleName,@enabled = @IsEnable,
@freq_type = @JobFreq, @freq_interval=@FreqInterval, @freq_recurrence_factor=@FreqFactor, @active_start_time=@StartTime
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- 添加作业执行Server
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = @ServerName 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION                  
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: