SQL Source Control 7

Create SQL Server Agent job

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 and RebuildIndices to ensure they are not created twice.
  • Post-Deployment scripts are deployed to every environment.
  • By default there is no error handling.

Didn't find what you were looking for?