SQL SERVER Create Job

--Define Create job
DECLARE @jobid uniqueidentifier
EXEC Msdb.dbo.sp_add_job
@job_name = N ' Job name ',
@job_id = @jobid OUTPUT

--Define job steps
DECLARE @sql nvarchar, @dbname sysname
SELECT @dbname =db_name (),--The job step is executed in the current database
@sql =n '--Job step content '--typically defines a job that is handled with TSQL, which defines the Transact-SQL statement to execute
EXEC Msdb.dbo.sp_add_jobstep
@job_id = @jobid,
@step_name = N ' job step name ',
@subsystem = ' TSQL ',--type of step, typically TSQL
@[email protected],
@command = @sql

--Create a schedule (using several job scheduling templates that are specifically defined later)
EXEC msdb: Sp_add_jobschedule
@job_id = @jobid,
@name = N ' dispatch name ',
@freq_type = 4,--Daily
@freq_interval = 1,--Specify how many days to occur, and this is 1 days.
@freq_subday_type =0x8,--repeat mode, 0x1= at the specified time, 0x4= how many minutes, 0x8= how many hours to execute once
@freq_subday_interval = 1,--repeats the number of cycles, which are performed once per hour
@active_start_date = NULL,--the start date of the job execution, which is null when the current date is formatted as YYYYMMDD
@active_end_date = 99991231,--stop date for job execution, default is 99991231, format YYYYMMDD
@active_start_time = 00000,--The start time of the job execution, in the format HHMMSS
@active_end_time = 235959-The stop time of the job execution, in the format HHMMSS

--Add target server
DECLARE @servername sysname
SET @servername =convert (nvarchar (+), serverproperty (N ' servername '))
EXEC Msdb.dbo.sp_add_jobserver
@job_id = @jobid,
@server_name = @servername--Using the current SQL instance


--Schedule Template definition
--/*--Day Dispatch
EXEC msdb: Sp_add_jobschedule
@job_id = @jobid,
@name = N ' dispatch name ',
@freq_type = 4,--Daily
@freq_interval = 1,--Specify how many days to occur, and this is 1 days.
@freq_subday_type =0x8,--repeat mode, 0x1= at the specified time, 0x4= how many minutes, 0x8= how many hours to execute once
@freq_subday_interval = 1,--repeats the number of cycles, which are performed once per hour
@active_start_date = NULL,--the start date of the job execution, which is null when the current date is formatted as YYYYMMDD
@active_end_date = 99991231,--stop date for job execution, default is 99991231, format YYYYMMDD
@active_start_time = 00000,--The start time of the job execution, in the format HHMMSS
@active_end_time = 235959-The stop time of the job execution, in the format HHMMSS
--*/

--/*--Week Schedule
EXEC Msdb.dbo.sp_add_jobschedule
@job_id = @jobid,
@name = N ' dispatch name ',
@freq_type = 8,--weekly
@freq_recurrence_factor = 1,--every few weeks, this is weekly
@freq_interval = 62,---in the day of the week, represented by POWER (2,n), the value of N is 0~6, which represents Sunday ~ Saturday, if two is specified, the value is added, for example, a value of 65 means execution in Sunday and Sunday (Power (2,0) +power (2 , 6))
@freq_subday_type = 0x8,--repeat mode, 0x1= at the specified time, 0x4= how many minutes, 0x8= how many hours to execute once
@freq_subday_interval = 1,--repeats the number of cycles, which are performed once per hour
@active_start_date = NULL,--the start date of the job execution, which is null when the current date is formatted as YYYYMMDD
@active_end_date = 99991231,--stop date for job execution, default is 99991231, format YYYYMMDD
@active_start_time = 00000,--The start time of the job execution, in the format HHMMSS
@active_end_time = 235959-The stop time of the job execution, in the format HHMMSS
--*/

--/*--Month Schedule (monthly number per x months)
EXEC Msdb.dbo.sp_add_jobschedule
@job_id = @jobid,
@name = N ' dispatch name ',
@freq_type = 16,--monthly
@freq_recurrence_factor = 2,--Every few months, this is every 2 months.
@freq_interval = 2,--executed on the day of the month of execution, this is the 2nd day
@freq_subday_type = 0x8,--repeat mode, 0x1= at the specified time, 0x4= how many minutes, 0x8= how many hours to execute once
@freq_subday_interval = 1,--repeats the number of cycles, which are performed once per hour
@active_start_date = NULL,--the start date of the job execution, which is null when the current date is formatted as YYYYMMDD
@active_end_date = 99991231,--stop date for job execution, default is 99991231, format YYYYMMDD
@active_start_time = 00000,--The start time of the job execution, in the format HHMMSS
@active_end_time = 235959-The stop time of the job execution, in the format HHMMSS
--*/

--/*--Month Schedule (relative time per x months)
EXEC Msdb.dbo.sp_add_jobschedule
@job_id = @jobid,
@name = N ' dispatch name ',
@freq_type = 32,--monthly
@freq_recurrence_factor = 2,--Every few months, this is every 2 months.
@freq_interval = 9,--executed at that time of the month, 1~7= Sunday to Saturday, 8= day, 9 = weekday, 10 = Weekend
@freq_relative_interval = 1,--performed at the first relative time, the allowable value is 1,2,4,8 for the 1th to 4th relative time, and 16 for the last relative time
@freq_subday_type = 0x8,--repeat mode, 0x1= at the specified time, 0x4= how many minutes, 0x8= how many hours to execute once
@freq_subday_interval = 1,--repeats the number of cycles, which are performed once per hour
@active_start_date = NULL,--the start date of the job execution, which is null when the current date is formatted as YYYYMMDD
@active_end_date = 99991231,--stop date for job execution, default is 99991231, format YYYYMMDD
@active_start_time = 00000,--The start time of the job execution, in the format HHMMSS
@active_end_time = 235959-The stop time of the job execution, in the format HHMMSS
--*/

--/*--job scheduling performed at a particular time
EXEC Msdb.dbo.sp_add_jobschedule
@job_id = @jobid,
@name = N ' dispatch name ',
@freq_type = 64--64= runs when the SQLServerAgent service starts, 128= when the computer is idle
--*/

--/*--job scheduling that only executes once
EXEC msdb: Sp_add_jobschedule
@job_id = @jobid,
@name = N ' dispatch name ',
@freq_type = 1,--executes only once
@active_start_date = NULL,--the start date of the job execution, which is null when the current date is formatted as YYYYMMDD
@active_start_time = 00000-The start time of the job execution, in the format HHMMSS
--*/

SQL SERVER Create Job