软件开发培训班 >> 编程开发 >> SQL


SQL SERVER数据库的作业的脚本及存储过程

 

IF EXISTS (SELECT name 
  FROM sysobjects 
  WHERE name = Ncg_DoBackupJob 
  AND  type = P)
  DROP PROCEDURE cg_DoBackupJob
 GO 
 

CREATE PROCEDURE [cg_DoBackupJob]
  @DataBaseName varchar(100),
  @FileHead  varchar(50),
  @isFullBackup bit,     -- 0 差量备份 1 完整备份
 @FolderPath  varchar(50)  = f:\db_backup\,
  @BackName varchar(100) = unknown,  -- 描述字串
 @isAppendMedia bit  = 1   -- 0 覆盖媒体 1 追加到媒体 

AS
  declare @filePath varchar(150)
  declare @sql varchar(1000)
  
  
  select @filePath=@FolderPath + @FileHead + _ + case @isFullBackup when 1 then FullBackup when 0 then DifferBackup end + _ + convert ( nvarchar(11) ,getdate() , 112 ) 
   + case @isFullBackup when 1 then  when 0 then replace(convert(nvarchar(15),getdate(),114),:,) end
  --print(@filePath)

 select @sql =BACKUP DATABASE [ + @DataBaseName + ] TO DISK =  
   + @filePath +  WITH 
   + case @isAppendMedia when 0 then INIT when 1 then NOINIT end 
   +  , NOUNLOAD , 
   + case @isFullBackup when 0 then DIFFERENTIAL ,  when 1 then  end 
   +  NAME = N + @BackName + 备份, NOSKIP , STATS = 10, NOFORMAT

 execute(@sql)
  --print(@sql)
 GO

-- =============================================
 -- example to execute the store procedure
 -- =============================================
 EXECUTE cg_DoBackupJob cg_access911,access911,1
 GO

用系统存储过程去创建作业，代码如下：

BEGIN TRANSACTION   
 DECLARE @JobID BINARY(16) 
 DECLARE @ReturnCode INT 
 SELECT @ReturnCode = 0  
 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N[Uncategorized (Local)]) < 1 
 EXECUTE msdb.dbo.sp_add_category @name = N[Uncategorized (Local)] 
 

 -- 删除同名的警报（如果有的话） 。
 SELECT @JobID = job_id  
 FROM msdb.dbo.sysjobs 
 WHERE (name = Naccess911_每2周备份一次)  
 IF (@JobID IS NOT NULL) 
 BEGIN 
 -- 检查此作业是否为多重服务器作业 
 IF (EXISTS (SELECT * 
    FROM msdb.dbo.sysjobservers 
    WHERE (job_id = @JobID) AND (server_id <> 0))) 
 BEGIN 
  -- 已经存在，因而终止脚本 
  RAISERROR (N无法导入作业“access911_每2周备份一次”，因为已经有相同名称的多重服务器作业 。, 16, 1) 
  GOTO QuitWithRollback 
 END 
 ELSE 
  -- 删除［本地］作业 
  EXECUTE msdb.dbo.sp_delete_job @job_name = Naccess911_每2周备份一次 
  SELECT @JobID = NULL
 END 

BEGIN 

 -- 添加作业
 EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = Naccess911_每2周备份一次, @owner_login_name = NAccess911\access911, @description = N没有可用的描述 。, @category_name = N[Uncategorized (Local)], @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

 -- 添加作业步骤
 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N2周备份, @command = NEXECUTE cg_DoBackupJob a9SupperDatabase,a9SupperDatabase,1
 , @database_name = Nmaster, @server = N, @database_user_name = N, @subsystem = NTSQL, @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N, @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
 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 = Ndiaodu, @enabled = 1, @freq_type = 8, @active_start_date = 20061009, @active_start_time = 0, @freq_interval = 64, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 2, @active_end_date = 99991231, @active_end_time = 235959
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

 -- 添加目标服务器
 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N(local) 
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

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

Transact-SQL 参考

sp_add_jobschedule
创建作业调度 。

语法
sp_add_jobschedule [ @job_id = ] job_id, | [ @job_name = ] job_name,
  [ @name = ] name
  [ , [ @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 = ] active_start_date ]
  [ , [ @active_end_date = ] active_end_date ]
  [ , [ @active_start_time = ] active_start_time ]
  [ , [ @active_end_time = ] active_end_time ]

参数
[ @jobid = ] job_id

将向其中添加调度的作业的作业标识号 。job_id 的数据类型为 uniqueidentifier，默认设置为 NULL 。

[ @job_name = ] job_name

作业的名称，调度即添加到该作业中 。job_name 的数据类型为 sysname，默认设置为 NULL 。

 

说明 必须指定 job_id 或 job_name，但不能两个都指定 。


[ @name = ] name

调度的名称 。name 的数据类型为 sysname，没有默认设置 。

[ @enabled = ] enabled

指明调度的当前状态 。enabled 的数据类型为 tinyint，默认设置为 1（启用） 。如果为 0，则不启用调度 。禁用该调度时，不运行作业 。

[ @freq_type = ] freq_type

用于指明何时将执行作业的值 。freq_type 的数据类型为 int，默认设置为 0，可以是下列值之一 。

值 描述 
1 一次 
4 每天 
8 每周 
16 每月 
32 每月，与 freq interval 相关 
64 当 SQLServerAgent 服务启动时运行 
128 计算机空闲时运行 


 [ @freq_interval = ] freq_interval

作业执行的天数 。freq_interval 的数据类型为 int，默认设置为 0，依赖于 freq_type 的值 。

 freq_type 的值 对 freq_interval 的影响 
1（一次） 未使用 freq_interval 。 
4（每天） 每个 freq_interval 日 。 
8（每周） freq_interval 为下面的一个或多个值（与 OR 逻辑运算符结合使用）： 
1 = 星期日
2 = 星期一
4 = 星期二
8 = 星期三
16 = 星期四
32 = 星期五
64 = 星期六
 
16（每月） 每月的 freq_interval 日 。 
32（每月相对） freq_interval 为下列值之一： 
1 = 星期日 
2 = 星期一 
3 = 星期二 
4 = 星期三 
5 = 星期四 
6 = 星期五 
7 = 星期六 
8 = 日 
9 = 工作日
10 = 周末
 
64（当 SQLServerAgent 服务启动时） 未使用 freq_interval 。 
128 未使用 freq_interval 。 


 [ @freq_subday_type = ] freq_subday_type

指定 freq_subday_interval 的单位 。freq_subday_type 为 int 类型，其默认值为 0，且可以取下列值之一 。

值 描述（单位） 
0x1 在指定的时间 
0x4 分钟 
0x8 小时 


 [ @freq_subday_interval = ] freq_subday_interval

作业每次执行之间要出现的 freq_subday_type 周期数 。freq_subday_interval 的数据类型为 int，默认设置为 0 。

[ @freq_relative_interval = ] freq_relative_interval

如果 freq_interval 是 32（每月相对），则为每月中已调度作业的 freq_interval 的发生情况 。freq_relative_interval 的数据类型为 int，默认设置为 0，可以是下列值之一 。

值 描述（单位） 
1 第一页 
2 秒 
4 第三个 
8 第四个 
16 最后一页 


 [ @freq_recurrence_factor = ] freq_recurrence_factor

作业的已调度执行之间的周数或月数 。只有当 freq_type 是 8、16 或 32 时，才使用 freq_recurrence_factor 。freq_recurrence_factor 的数据类型为 int，默认设置为 0 。

[ @active_start_date = ] active_start_date

作业可开始执行的日期 。active_start_date 的数据类型为 int，默认设置为 NULL，该值表示当天的日期 。日期的格式为 YYYYMMDD 。如果 active_start_date 不为 NULL，则日期必须大于或等于 19900101 。

[ @active_end_date = ] active_end_date

作业可停止执行的日期 。active_end_date 的数据类型为 int，默认设置为 99991231，该值表示 9999 年 12 月 31 日 。格式为 YYYYMMDD 。

[ @active_start_time = ] active_start_time

在 active_start_date 和 active_end_date 之间的任何一天开始执行作业的时间 。active_start_time 的数据类型为 int，默认设置为 000000，该值表示 24 小时制的上午 12:00:00，并且必须使用格式 HHMMSS 进行输入 。

[ @active_end_time = ] active_end_time

在 active_start_date 和 active_end_date 之间的任何一天停止执行作业的时间 。active_end_time 的数据类型为 int，默认设置为 235959，该值表示 24 小时制的下午 11:59:59，并且必须使用格式 HHMMSS 进行输入 。

返回代码值
0（成功）或 1（失败）

结果集
 无

注释
SQL Server 企业管理器提供易于使用的图形方法来管理作业，建议使用该方法创建和管理作业基本结构 。

权限
 执行权限默认授予 public 角色 。

示例
 此示例假设已经创建用来备份数据库的 NightlyBackup 作业 。它将作业添加到名为 ScheduledBackup 的调度中，并且在每天上午 1:00 执行 。

USE msdb
 EXEC sp_add_jobschedule @job_name = NightlyBackup, 
 @name = ScheduledBackup,
 @freq_type = 4, -- daily
 @freq_interval = 1,
 @active_start_time = 10000


请参见

修改和查看作业

sp_delete_jobschedule

sp_help_jobschedule

sp_update_jobschedule

系统存储过程

本站文章旨在为该问题提供解决思路及关键性代码，并不能完成应该由网友自己完成的所有工作，请网友在仔细看文章并理解思路的基础上举一反三、灵活运用 。