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.