The script and stored procedure of SQL Server database job

Time:2020-10-28
IF EXISTS (SELECT name 
  FROM sysobjects 
  WHERE name = N'cg_DoBackupJob' 
  AND  type = 'P')
  DROP PROCEDURE cg_DoBackupJob
 GO 
 

CREATE PROCEDURE [cg_DoBackupJob]
  @DataBaseName varchar(100),
  @FileHead  varchar(50),
  @Isfullbackup bit, - 0 differential backup 1 full backup
 @FolderPath  varchar(50)  = 'f:\db_backup\',
  @Backname varchar (100) ='unknown ', -- description string
 @Isappendmedia bit = 1 -- 0overlay media 1 append to media 

AS
  declare @filePath varchar(150)
  declare @sql varchar(1000)
  
  
  select @[email protected] + @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 +'backup '', noskip, stats = 10, noformat '

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

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

Use the system stored procedure to create a job. The code is as follows:

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)]' 
 

 --Delete the alert with the same name, if any.
 SELECT @JobID = job_id  
 FROM msdb.dbo.sysjobs 
 WHERE (name = N'access911_ Backup every 2 weeks')  
 IF (@JobID IS NOT NULL) 
 BEGIN 
 --Check if this job is a multiple server job 
 IF (EXISTS (SELECT * 
    FROM msdb.dbo.sysjobservers 
    WHERE (job_id = @JobID) AND (server_id <> 0))) 
 BEGIN 
  --The script is terminated because it already exists 
  Raiserror (n 'cannot import job "access911"_ Backup every 2 weeks "because there are already multiple server jobs with the same name. ', 16, 1) 
  GOTO QuitWithRollback 
 END 
 ELSE 
  --Delete [local] operation 
  EXECUTE  msdb.dbo.sp_ delete_ job @job_ name = N'access911_ Backup every 2 weeks' 
  SELECT @JobID = NULL
 END 

BEGIN 

 --Add job
 EXECUTE @ReturnCode =  msdb.dbo.sp_ add_ job @job_ id = @JobID OUTPUT , @job_ name = N'access911_ Backup every 2 weeks', @ owner_ login_ Name = n'access911 / access911 ', @ description = n' no description available. ', @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 

 --Add job步骤
 EXECUTE @ReturnCode =  msdb.dbo.sp_ add_ jobstep @job_ id = @JobID, @step_ id = 1, @step_ Name = n'2-week backup ', @ command = n'execute CG_ DoBackupJob ''a9SupperDatabase'',''a9SupperDatabase'',1
 ', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @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 

 --Add job调度
 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'diaodu', @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 

 --Add target server
 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 reference

sp_add_jobschedule
Create a job schedule.

grammar
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 ]

parameter
[ @jobid = ] job_id

The job ID number of the job to which the scheduled job will be added. job_ The data type of ID is uniqueidentifier, which is set to null by default.

[ @job_name = ] 'job_name'

The name of the job, and the schedule is added to the job. job_ The data type of name is sysname, which is set to null by default.

 

Description must specify a job_ ID or job_ Name, but not both.


[ @name = ] 'name'

The name of the schedule. The data type of name is sysname and there is no default setting.

[ @enabled = ] enabled

Indicates the current state of the schedule. The data type of enabled is tinyint, which is set to 1 (enabled) by default. If 0, scheduling is not enabled. When the schedule is disabled, the job does not run.

[ @freq_type = ] freq_type

A value that indicates when the job will be executed. freq_ The data type of type is int, which is set to 0 by default and can be one of the following values.

Value description 
1 time 
4 every day 
8 weekly 
16 monthly 
32 monthly, related to the freq interval 
Run when the SQL server agent service is started 
128 computers run when idle 


 [ @freq_interval = ] freq_interval

The number of days the job was executed. freq_ The data type of interval is int, which is set to 0 by default and depends on freq_ Type.

 freq_ The value pair of type is freq_ The influence of interval 
1 (once) not using freq_ interval。 
4 (daily) per freq_ Interval day. 
8 (weekly) freq_ Interval is one or more of the following values (used in combination with the or logical operator): 
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday
 
16 (monthly) monthly freq_ Interval day. 
32 (monthly relative) freq_ Interval is one of the following values: 
1 = Sunday 
2 = Monday 
3 = Tuesday 
4 = Wednesday 
5 = Thursday 
6 = Friday 
7 = Saturday 
8 = day 
9 = weekday
10 = weekend
 
64 (when the SQL server agent service starts) does not use freq_ interval。 
128 not using freq_ interval。 


 [ @freq_subday_type = ] freq_subday_type

Specify freq_ subday_ Unit of interval. freq_ subday_ Type is an int type with a default value of 0 and can take one of the following values.

Value description(单位) 
At the specified time 
0x4 minutes 
0x8 hours 


 [ @freq_subday_interval = ] freq_subday_interval

The freq to appear between each execution of the job_ subday_ Type number of cycles. freq_ subday_ The data type of interval is int, which is set to 0 by default.

[ @freq_relative_interval = ] freq_relative_interval

If freq_ If the interval is 32 (monthly relative), it is the freq of the scheduled jobs in the month_ The occurrence of interval. freq_ relative_ The data type of interval is int, which is set to 0 by default and can be one of the following values.

Value description(单位) 
1 first page 
2 seconds 
4 the third 
The fourth 
16 last page 


 [ @freq_recurrence_factor = ] freq_recurrence_factor

The number of weeks or months between scheduled execution of a job. Only when freq_ Use freq only when type is 8, 16, or 32_ recurrence_ factor。 freq_ recurrence_ The data type of factor is int, which is set to 0 by default.

[ @active_start_date = ] active_start_date

The date on which the job can begin execution. active_ start_ The data type of date is int, which is set to null by default. This value represents the date of the day. The format of the date is yyyymmdd. If active_ start_ If date is not null, the date must be greater than or equal to 19900101.

[ @active_end_date = ] active_end_date

The date on which the job can stop execution. active_ end_ The data type of date is int, and the default setting is 99991231, which indicates December 31, 9999. The format is yyyymmdd.

[ @active_start_time = ] active_start_time

In active_ start_ Date and active_ end_ The time at which the job begins to execute on any day between date. active_ start_ The data type of time is int, which is set to 000000 by default. The value represents 12:00:00 a.m. in a 24-hour format, and must be entered in the format hhmmss.

[ @active_end_time = ] active_end_time

In active_ start_ Date and active_ end_ The time at which the job stops executing on any day between date. active_ end_ The data type of time is int, which is set to 235959 by default. This value represents the 24-hour 11:59:59 PM and must be entered in the format hhmmss.

Return code value
Success (0) or failure

Result set
 nothing

notes
SQL Server Enterprise Manager provides an easy-to-use graphical method to manage jobs, which is recommended to create and manage job infrastructure.

jurisdiction
 执行jurisdiction默认授予 public 角色。

Examples
 此Examples假设已经创建用来备份数据库的 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


See

Modify and view jobs

sp_delete_jobschedule

sp_help_jobschedule

sp_update_jobschedule

System stored procedure

The purpose of this article is to provide solutions and key codes for this problem, and it can not complete all the work that should be done by netizens themselves. Please draw inferences from one instance and flexibly use them on the basis of carefully reading the article and understanding the ideas.