Job Script:

We can drop or create jobs.

Can we alter the Jobs?  --Yes we can but not using ALTER keyword.

We have different SPs to alter the job properties like schehules, steps...etc  

Some are here-

-- creates a job step that that uses Transact-SQL

USE msdb;

GO

EXEC sp_add_jobstep

@job_name = N'Weekly Sales Data Backup',

@step_name = N'Set database to read only',

@subsystem = N'TSQL',

@command = N'ALTER DATABASE SALES SET READ_ONLY',

@retry_attempts = 5,

@retry_interval = 5 ;

GO

--To drop existing schedule

USE msdb ;

GO

EXEC dbo.sp_detach_schedule

    @job_name = 'START BATCH - DSAS',

    @schedule_name = 'START BATCH - DSAS' ;

GO

--To create new schedule

DECLARE @jobId BINARY(16)

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

set @jobId=(SELECT JOB_ID FROM msdb.dbo.sysjobs where name like '%START BATCH - DSAS%')

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'START BATCH - DSAS',

            @enabled=1,

            @freq_type=8,

            @freq_interval=127,

            @freq_subday_type=1,

            @freq_subday_interval=0,

            @freq_relative_interval=0,

            @freq_recurrence_factor=1,

            @active_start_date=20120417,

            @active_end_date=99991231,

            @active_start_time=10000,

            @active_end_time=235959,

            @schedule_uid=N'e3633b86-f1a9-401e-9459-c53e6302764b'

-->Each Job will have a unique id (check job ids: select job_id from msdb.dbo.sysjobs) likewise each shedule have a uniqueid. If we try to use existing schedule Id for another new schedule then both schedule times effects for the new schedule job.

--> why we are not going for drop and create of a job instead altering the job properties?

Ans: there are several reasons for this query. You can have a consistent job id every time, even job properties changes also.

Other SPs used here are:

sp_add_category

sp_add_job

sp_update_job

sp_delete_job

sp_help_job-->Returns information about jobs that are used by SQL Server Agent to perform automated activities in SQL Server. 

sp_update_jobstep -> Changes the setting for a step in a job that is used to perform automated activities.

sp_start_job--> Instructs SQL Server Agent to execute a job immediately.

sp_stop_job-->Instructs SQL Server Agent to stop the execution of a job.

SP_GET_COMPOSITE_JOB_INFO-->to identify the currently running jobs.

Disable All SQL Server Agent Jobs:

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

Enable All SQL Server Agent Jobs:

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0;
GO

Disable Jobs By Job Name:

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'Admin%';
GO

Disable Jobs By Job Category:

USE MSDB;
GO
UPDATE J
SET J.Enabled = 0
FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.[Name] = 'Database Maintenance';
GO

---------------------------------