SQL server database job scripts and stored procedures, SQL SERVER

SQL server database job scripts and stored procedures, SQL SERVER

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 complete backup @ FolderPath varchar (50) = 'f: \ db_backup \ ', @ BackName varchar (100) = 'unknown ', -- Description string @ isAppendMedia bit = 1 -- 0 overwrite media 1 append to media 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 + '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 an alert with the same name (if any ). SELECT @ JobID = job_id FROM msdb. dbo. sysjobs WHERE (name = n' access911_back up every two weeks ') IF (@ JobID is not null) BEGIN -- check whether this job is a multi-server job IF (EXISTS (SELECT * FROM msdb. dbo. sysjobservers WHERE (job_id = @ JobID) AND (server_id <> 0) BEGIN -- already exists, therefore, the script RAISERROR (n' cannot be imported into the job "access911_every two weeks, because multiple server jobs with the same name already exist. ', 16, 1) GOTO QuitWithRollback end else -- delete the [local] job EXECUTE msdb. dbo. sp_delete_job @ job_name = n' access911_back up every two weeks 'select @ JobID = null end begin -- add Job EXECUTE @ ReturnCode = msdb. dbo. sp_add_job @ job_id = @ JobID OUTPUT, @ job_name = n' access911_back up every two weeks ', @ owner_login_name = n' access911 \ access911 ', @ description = n' there is no available description. ', @ Category_name = n' [Uncategorized (Local)]', @ enabled = 1, @ yy_level_email = 0, @ yy_level_page = 0, @ resolve = 0, @ yy_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 '', ''a9supperdatabas'', 1', @ database_name = n'master', @ server = n'', @ database_user_name = n'', @ subsystem = n'tsql ', @ override = 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 Scheduling 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 the 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 job scheduling. 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 =] interval] [, [@ freq_relative_interval =] freq_relative_interval] [, [@ freq_recurrence_factor =] freq_recurr Ence_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 adds the job ID of the scheduled job to it. The data type of job_id is uniqueidentifier. The default value is NULL. [@ Job_name =] name of the 'job _ name' job. Scheduling is added to the job. The data type of job_name is sysname. The default value is NULL. Job_id or job_name must be specified, but not both. [@ Name =] name of the 'name' scheduling. The data type of name is sysname, which is not set by default. [@ Enabled =] enabled indicates the current scheduling status. The data type of enabled is tinyint. The default value is 1 (enabled ). If the value is 0, scheduling is not enabled. When the scheduling is disabled, the job is not run. [@ Freq_type =] freq_type indicates the value of when the job will be executed. The data type of freq_type is int, and the default value is 0. It can be one of the following values. Value description: 1 time 4 day 8 weeks 16 months 32 months, related to freq interval 64 when the SQLServerAgent service starts 128 when the computer is idle run [@ freq_interval =] freq_interval job execution days. The data type of freq_interval is int. The default value is 0, which depends on the value of freq_type. The Influence of freq_type value on freq_interval is 1 (once). freq_interval is not used. 4 (daily) Every 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) freq_interval of every month. 32 (relative to each month) freq_interval is one of the following values: 1 = Sunday 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thursday 6 = Friday 7 = Saturday 8 = Thursday 9 = workday 10 = weekend 64 (when the SQLServerAgent service starts) freq_interval is not used. 128 freq_interval is not used. [@ Freq_subday_type =] freq_subday_type specifies the unit of freq_subday_interval. Freq_subday_type is of the int type. The default value is 0 and one of the following values can be used. Value description (unit) 0x1 The number of freq_subday_type cycles that will appear between each execution of the specified time 0x4 minutes 0x8 hours [@ freq_subday_interval =] freq_subday_type. The data type of freq_subday_interval is int, and the default value is 0. [@ Freq_relative_interval =] freq_relative_interval if freq_interval is 32 (relative to each month), it is the occurrence of freq_interval for jobs scheduled in each month. The data type of freq_relative_interval is int. The default value is 0, which can be one of the following values. Value description (unit) 1 first page 2 seconds 4 Third 8 fourth 16 last page [@ freq_recurrence_factor =] number of weeks or months between scheduled executions of the freq_recurrence_factor job. Freq_recurrence_factor is used only when freq_type is 8, 16, or 32. The data type of freq_recurrence_factor is int, and the default value is 0. [@ Active_start_date =] Date on which the active_start_date job can start to run. The data type of active_start_date is int. The default value is NULL. This value indicates the date of the current day. The date format is YYYYMMDD. If active_start_date is not NULL, the date must be greater than or equal to 19900101. [@ Active_end_date =] Date on which the active_end_date job can be stopped. The data type of active_end_date is int. The default value is 99991231. This value indicates January 1, December 31, 9999. The format is YYYYMMDD. [@ Active_start_time =] the time when the job starts to be executed on any day between active_start_date and active_end_date. The data type of active_start_time is int. The default value is 000000. This value indicates 12:00:00 am in 24-hour format and must be input in HHMMSS format. [@ Active_end_time =] the time when the job is stopped on any day between active_start_date and active_end_date. The data type of active_end_time is int. The default value is 235959. This value indicates 11:59:59 in the 24-hour format and must be input in HHMMSS format. The return code value is 0 (successful) or 1 (failed). The result set is not annotated. the SQL Server Enterprise Manager provides easy-to-use graphical methods for job management. We recommend that you use this method to create and manage the basic structure of a job. The permission execution permission is granted to the public role by default. In this example, we assume that you have created a root backup job to back up the database. It adds the job to the scheduling named ScheduledBackup and runs it at every morning. 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 the stored procedure of the job sp_delete_jobschedulesp_help_jobschedulesp_update_jobschedule.

This article aims to provide solutions and key code for this problem, and cannot complete all the work that should be done by netizens themselves, please read the article carefully and understand the ideas.