Scripts and stored procedures for jobs in SQL Server databases _mssql

IF EXISTS (SELECT name from sysobjects WHERE name = N ' Cg_dobackupjob ' and type = ' P ') DROP PROCEDURE Cg_dobac Kupjob go CREATE PROCEDURE [cg_dobackupjob] @DataBaseName varchar (@FileHead varchar), @isFullBackup b It,-0 differential backup 1 full backup @FolderPath varchar = ' f:\db_backup\ ', @BackName varchar = ' unknown ',--description string @is  Appendmedia bit = 1--0 Overlay Media 1 append to media as declare @filePath varchar () declare @sql varchar (1000) Select @filePath = @FolderPath + @FileHead + ' _ ' + case @isFullBackup when 1 Then ' Fullbackup ' when 0 Then ' differbackup ' + ' _ ' + convert (nvarchar (one), GETDATE (), 112) + Case @isFullBackup when 1 then "when 0 then replace (convert (nvarchar  
   (), 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 @isFullBackupWhen 0 Then ' differential, ' if 1 Then ' end + ' NAME = N ' ' + @BackName + ' backup ', noskip, STATS = ten, Noformat ' Execute (@sql)--print (@sql) Go-=============================================-Example to execute the store proc
 Edure--============================================= EXECUTE cg_dobackupjob ' cg_access911 ', ' access911 ', 1 go

Use the system stored procedure to create a job with the following code:

BEGIN TRANSACTION DECLARE @JobID BINARY () 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)] '--Deletes an alert with the same name (if any). 
 SELECT @JobID = job_id from Msdb.dbo.sysjobs WHERE (name = N ' access911_ backup once 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 &L t;> 0)) BEGIN-already exists, thus terminating the script RAISERROR (N ' cannot import job "access911_ backup every 2 weeks" because multiple server jobs with the same name already exist.) ', 1) GOTO Quitwithrollback end ELSE--delete [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N ' access911_ per  2 Weekly backup ' 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 step 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 ', @data Base_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 & lt;> 0 OR @ReturnCode <> 0) GOTO quitwithrollback--Add job schedule 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 = @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 @ReturnC Ode <> 0) GOTO quitwithrollback--Add target server EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @se    
 Rver_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 job schedules. Syntax 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_interva l] [, [@freq_relative_interval =] freq_relative_interval] [, [@freq_recurrence_factor =] Freq_recurrence_facto R] [, [@active_start_date =] active_start_date] [, [@active_end_date =] active_end_date] [, [@active_star T_time =] active_start_time] [, [@active_end_time =] active_end_time] parameter [@jobid =] job_id The job identification number to which the scheduled job will be added.

The job_id data type is uniqueidentifier and the default setting is NULL. [@job_name =] The name of the ' job_name ' job, where the schedule is added to the job.

 

The job_name data type is sysname and the default setting is NULL.


The description must specify either job_id or Job_name, but not both. [@name =] Name of the ' name ' dispatch.

The data type of name is sysname and there is no default setting. [@enabled =] enabled indicates the current state of the schedule. The enabled data type is tinyint, and the default setting is 1 (enabled). If 0, no rebootWith scheduling.

When this schedule is disabled, the job is not run. [@freq_type =] freq_type is used to indicate when the value of the job will be executed.

The Freq_type data type is int, and the default setting is 0, which can be one of the following values. Value Description 11 times 4 per day 8 per week 16 per month 32 per month, with Freq interval related 64 when SQLServerAgent service starts running 128 computer idle run [@freq_interval = ] Freq_interval the number of days the job executes.

 The freq_interval data type is int, and the default setting is 0, which depends on the freq_type value. 
The effect of Freq_type value on Freq_interval 1 (once) is not used freq_interval. 
4 (per day) every Freq_interval day. 8 (weekly) freq_interval for one or more of the following values (used in conjunction with or logical operators): 1 = Sunday 2 = Monday 4 = Tuesday 8 = Wednesday 16 = Thursday 32 = Friday 64 = Saturday 16 (monthly) F per month 
Req_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 
Freq_interval is not used (when the SQLServerAgent service is started). 


 128 does not use Freq_interval. [@freq_subday_type =] freq_subday_type Specifies the unit of the freq_subday_interval.

Freq_subday_type is an int type with a default value of 0 and can take one of the following values. Value Description (unit) 0x1 0x4 minutes 0x8 hours at a specified time [@freq_subday_interval =] Freq_subday_interval The freq_subday_type week to occur between each execution of the job Number of periods. The Freq_subday_interval data type is int, and the default setting is0. [@freq_relative_interval =] freq_relative_interval If the freq_interval is 32 (monthly relative), the freq_interval of the scheduled job in each month.

The Freq_relative_interval data type is int, and the default setting is 0, which can be one of the following values. Value Description (unit) 1 first page 2 Second 4 third 8 fourth 16 last page [@freq_recurrence_factor =] The number of weeks or months between scheduled executions of the Freq_recurrence_factor job. Use Freq_recurrence_factor only when Freq_type is 8, 16, or 32 o'clock.

The Freq_recurrence_factor data type is int and the default setting is 0. [@active_start_date =] active_start_date The date the job can begin execution. The active_start_date data type is int, and the default setting is NULL, which represents the day's date. The format of the date is YYYYMMDD.

If active_start_date is not NULL, the date must be greater than or equal to 19900101. [@active_end_date =] active_end_date The date the job can stop executing. The active_end_date data type is int, and the default setting is 99991231, which indicates December 31, 9999.

The format is YYYYMMDD. [@active_start_time =] Active_start_time The time of the execution of the job on any day between Active_start_date and Active_end_date.

The Active_start_time data type is int, the default setting is 000000, which represents 12:00:00 in the 24-hour system, and must be entered using a format HHMMSS. [@active_end_time =] active_end_time time to stop the execution of the job on any day between Active_start_date and Active_end_date. The Active_end_time data type is int, the default setting is 235959, which indicates a 24-hour 11:59:59, and must be entered using format HHMMSS.

Return code value 0 (success) or 1 (failed) result set no comment SQL Server Enterprise Manager provides easy-to-use graphical methods to manage jobs, and it is recommended that you create and manage the job infrastructure.

Permission execution permissions are granted to the public role by default. Example This example assumes that a nightlybackup job has been created to back up the database.

It adds the job to the schedule named Scheduledbackup and executes at 1:00 every day. Use msdb EXEC sp_add_jobschedule @job_name = ' Nightlybackup ', @name = ' scheduledbackup ', @freq_type = 4, the daily @fr Eq_interval = 1, @active_start_time = 10000 See Modify and view Job Sp_delete_jobschedule sp_help_jobschedule Sp_update_jobsche
 Dule System Stored Procedures

This site to provide solutions to the problem of ideas and key code, and can not be completed by the user to complete all the work, please read the article carefully and understand the basis of the idea of extrapolate, flexible use.