Creating a regularly scheduled job

The goal of this post is to show how you can create a schedule and attach an existing job to that schedule.

Create a schedule on an existing job (or create a job as described in a number of previous posts).

1) Open SQL Server Manament Studio (SSMS), connect to SQL Server, and right click on the Sql Server Agent \ Jobs \ Your Job.

Select Properties and click.1

2) Click on the Schedules Page on the left, then click New… under Schedule list.

If the job already has a schedule, you can edit it or remove it.



3) Enter a name for the schedule, then select the type of schedule you want.

Here is some information:

Schedule Type: the option are straightforward. For “Start whenever the CPUs become idle” you need to set up an idle CPU condition (see

Enabled: here you can disable a schedule without deleting it.

You can select what type of occurrence, the days of the week and/or how many days/weeks/months to skip between executions.

For daily schedules, you can specify how many seconds/minutes or hours between executions.

Note: By default, Sql Agent will not execute a job more often than four times a minute (every 15 seconds). In general, the scheduler cannot be used for “real time” events. The job start can be delayed depending on the system load.



Check that the next job execution will be triggered at the right time.

After creating or updating a schedule, you can check the next execution date and time.

Preconditions: Sql Agent has to be running for the next execution date/time to be calculated.

In SSMS, expand the Sql Server Agent node under Object Explorer, then double-click on Job Activity Monitor. Look for your job name and check the column “Next Run”.


Check the job was executed at the right time after the scheduled time has passed.

You can check the job history at any time to confirm that the schedule was triggered correctly and the job succeeded.

Just right click on the job and select “View History”.


This posting is provided "AS IS" with no warranties, and confers no rights. 
Use of included script samples are subject to the terms specified at

Comments (10)

  1. AaronLS says:

    Is the Job History cleared whenever the log is truncated? "BACKUP * WITH … TRUNCATE"  Where I work they've decided to truncate logs instead of backing them up.  I have noticed that the agent job I setup here to run over the weekend doesn't show any job history.  It does however indicate that it was run in the "Last Executed" field.

  2. sahar says:

    hi,thanks for your Instructions

    but when i want to backing up from a DB every day automaticly,The second will overwrite the previous backup.

    i want to have one Separated back up every day

    please help me

  3. stephen says:

    Hi sahar. This will help you

    DECLARE @name VARCHAR(50) — database name  

    DECLARE @path VARCHAR(256) — path for backup files  

    DECLARE @fileName VARCHAR(256) — filename for backup  

    DECLARE @fileDate VARCHAR(20) — used for file name

    — specify database backup directory

    SET @path = 'G:DATA BACKUP'  

    — specify filename format

    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

    SET @name='test';

    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  

    BACKUP DATABASE @name TO DISK = @fileName  

  4. Berni says:


    What to do if there is no SQL server agent folder at all?  The last one is Management. 🙁

    It is an SQL Server 2008 R2 and management studio: 10.50.4000.0


  5. Pooja Sharma says:

    Can we call a stored procedure daily at a fixed time for a fixed duration throuh scheduler. if it so, then please tell me the steps.

  6. jaspreet says:

    nice but need to have more clarity on information provided.

  7. Harini says:

    After Successful complition of one job scheduler i need to start the another job in another database how can i do it?

    is that possible through coding or should i go with the wizard as you shown above.

  8. kumar says:

    how to get the email alerts for particular job.

  9. venkatesan says:

    its useful for me.thanks sir

  10. VIPS says:

    Hi I have requirement to run the job every month once, but only on two day before last working day of month. if in Sept, 30 is on Sunday then job should run on Wednesday, as last two working days are Thursday and Friday.

Skip to main content