Create SQL Server Agent job
Published 20 A 2018
Post-Deployment script to create a SQL Server Agent job
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = N'NightlyIndexRebuilds') BEGIN PRINT N'Adding SQL Server Agent job NightlyIndexRebuilds' EXEC sp_executesql N'USE msdb; EXEC dbo.sp_add_job @job_name = N''NightlyIndexRebuilds''' END GO IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE step_name = N'RebuildIndices' AND job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'NightlyIndexRebuilds')) BEGIN PRINT N'Adding SQL Server Agent job step RebuildIndices to job NightlyIndexRebuilds' EXEC sp_executesql N'USE msdb; EXEC dbo.sp_add_jobstep @job_name = N''NightlyIndexRebuilds'', @step_name = N''RebuildIndices'', @subsystem = N''TSQL'', @command = N''EXEC sp_example_sproc_to_rebuild_indices'', @retry_attempts = 5, @retry_interval = 5' END GO
Notable points:
- Post-Deployment scripts must be re-runnable as they will be executed as part of every deployment.
- Object existence checks are used for both
NightlyIndexRebuilds
andRebuildIndices
to ensure they are not created twice.
- Object existence checks are used for both
- Post-Deployment scripts are deployed to every environment.
- By default there is no error handling.