Schedule a SQLAgent job using t-sql

Question

Post reply

Schedule a SQLAgent job using t-sql

gary1

SSC-Insane

Points: 21152
More actions
October 8, 2010 at 8:12 am

#140998

Hi,
I have backup job called TestBackup and I want to schedule it to run at 3:00 PM using t-sql.
I know how to do this from SSMS. But I want to schedule this job using t-sql.
Please advice
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can script any job and check the script to see how to schedule a job or read in BOL about sp_add_jobschedule

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Adiga One Orange Chip Points: 27284 More actions · Answer 2

Right click on an existing job and select "Script job as". This will create the script for you. You may use this script as an example and do the changes as needed

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

gary1 SSC-Insane Points: 21152 More actions · Answer 3

Thank you,

I ran the below query to schedule the job Test_Backup to run at 11:45 AM.

USE msdb
EXEC sp_add_jobschedule @job_name = 'Test_Backup',
@name = 'Full',
@freq_type = 1, -- daily
@freq_interval = 1,
@active_start_time = 114500

The job Test_Backup has already have job step (Test_Backup->Properties->Schedule->here, under schedule list, I have a scheduled item that configured earlier named as Full and when I ran the above query it adding one more step under schedule list to run at 11:45 am.

Is there way not to create one more step under schedule list and overwrite the existing schedule? please see the attachment.

Thank you

george sibbald SSC Guru Points: 104210 More actions · Answer 4

If you want to update the scheduled time via tsql use sp_update_jobschedule

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