SQL Theory: Schedule a Job (For example: Creating a database backup schedule)

Schedule a Job 

For example: Creating a database backup schedule

SQL Server 2014

This topic describes how to schedule a SQL Server Agent job.

Before You Begin

Security

For detailed information, see Implement SQL Server Agent Security.

Using SQL Server Management Studio

To create and attach a schedule to a job

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
  2. Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.
  3. Select the Schedules page, and then click New.
  4. In the Name box, type a name for the new schedule.
  5. Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation.
  6. For Schedule Type, select one of the following:
    • Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.
    • Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition.
    • Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the FrequencyDaily Frequency, and Duration groups on the dialog.
    • Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog.

To attach a schedule to a job

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
  2. Expand SQL Server Agent, expand Jobs, right-click the job that you want to schedule, and click Properties.
  3. Select the Schedules page, and then click Pick.
  4. Select the schedule that you want to attach, and then click OK.
  5. In the Job Properties dialog box, double-click the attached schedule.
  6. Verify that Start date is set correctly. If it is not, set the date when you want for the schedule to start, and then click OK.
  7. In the Job Properties dialog box, click OK.

Using Transact-SQL

To schedule a job

  1. In Object Explorer, connect to an instance of Database Engine.
  2. On the Standard bar, click New Query.
  3. Copy and paste the following example into the query window and click Execute.
    USE msdb ;
    GO
    -- creates a schedule named NightlyJobs.
    -- Jobs that use this schedule execute every day when the time on the server is 01:00.
    EXEC sp_add_schedule
    @schedule_name = N'NightlyJobs' ,
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 010000 ;
    GO
    -- attaches the schedule to the job BackupDatabase
    EXEC sp_attach_schedule
    @job_name = N'BackupDatabase',
    @schedule_name = N'NightlyJobs' ;
    GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s