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
|
1 2 3 4 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
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 |
