Introduction

As per business requirements, a report needs to generate four times every month. The report will generated by a Stored Procedure. So the DBA’s task is to execute the specific Stored Procedure on a few specific dates of every month by an automated process. The simplest process is to create a job with four different schedules within a job for a single task.

For demonstration, we will use a backup script in place of the mentioned Stored Procedure to generate the report and run the same every month on specific four dates of every month.

Creating Job by SSMS

First of all, we will do the activity by SSMS. Connect to the SQL Server using SSMS. Right-click on SQL Server Agent > New > Job

In General section Name: [Name of the Job], Owner: [sa is recomentaded], Category: as per you.

In Steps section, click the New button at the left down corner of the window.

Here we need to fill Step name, select Type, Select Database name & Paste Command

Here we have use backup script in command

BACKUP DATABASE [Our_Tech_Ideas] TO  DISK = N'F:\SQL DATABASE\Gama\Our_Tech_Ideas_FullBackup.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'Our_Tech_Ideas-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

In Schedules section, click the New button at the left down corner of the window.

First schedule for the task. Provide Name (any name), Schedule type (Recurring), Frequency – Occurs (Monthly), Day (Date as per requirement), Daily frequency (Time for execution). So we have provided 1st date of the month & time at 9 am.

Second schedule for the task. Provide Name (any name), Schedule type (Recurring), Frequency – Occurs (Monthly), Day (Date as per requirement), Daily frequency (Time for execution). So we have provided 8th date of the month & time at 9 am.

Third schedule for the task. Provide Name (any name), Schedule type (Recurring), Frequency – Occurs (Monthly), Day (Date as per requirement), Daily frequency (Time for execution). So we have provided 15th date of the month & time at 9 am.

Fourth schedule for the task. Provide Name (any name), Schedule type (Recurring), Frequency – Occurs (Monthly), Day (Date as per requirement), Daily frequency (Time for execution). So we have provided 25th date of the month & time at 9 am.

All four schedules created

Single Job with a single task with four different dates schedule is created

Creating Job by TSQL

USE [msdb]
GO

/****** Object:  Job [4 Backup in a month on fixed date]    Script Date: 16-10-2019 23:10:22 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 16-10-2019 23:10:23 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'4 Backup in a month on fixed date', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'Database Maintenance', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Backup user database in 4 dates]    Script Date: 16-10-2019 23:10:23 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup user database in 4 dates', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'BACKUP DATABASE [Our_Tech_Ideas] TO  DISK = N''F:\SQL DATABASE\Gama\Our_Tech_Ideas_FullBackup.bak'' 
WITH NOFORMAT, NOINIT,  NAME = N''Our_Tech_Ideas-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Date 1', 
		@enabled=1, 
		@freq_type=16, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20191016, 
		@active_end_date=99991231, 
		@active_start_time=90000, 
		@active_end_time=235959, 
		@schedule_uid=N'89878f49-1976-4ed9-ae27-c0862eef162c'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Date 15', 
		@enabled=1, 
		@freq_type=16, 
		@freq_interval=15, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20191016, 
		@active_end_date=99991231, 
		@active_start_time=90000, 
		@active_end_time=235959, 
		@schedule_uid=N'ba80e754-c149-4235-8aa9-40ebe40e7f68'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Date 25', 
		@enabled=1, 
		@freq_type=16, 
		@freq_interval=25, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20191016, 
		@active_end_date=99991231, 
		@active_start_time=90000, 
		@active_end_time=235959, 
		@schedule_uid=N'11daf32b-0d7b-449b-bc57-85705073ac62'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Date 8', 
		@enabled=1, 
		@freq_type=16, 
		@freq_interval=8, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20191016, 
		@active_end_date=99991231, 
		@active_start_time=90000, 
		@active_end_time=235959, 
		@schedule_uid=N'aaec5c31-408c-491a-8dd9-74dd4c8b143e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO