Scheduling a Backup

To ensure that your databases and logs are regularly backed up, you can use SQL Enterprise Manager to schedule backups. Scheduled backups can be set to occur one time or on a recurring basis. Recurring backups can be set to occur hourly, daily, weekly, or monthly.

    To schedule a backup

1. Define the backup as described in Backing Up a Database or Transaction Log, earlier in this chapter.
2. From the Database Backup/Restore window, choose the Schedule button.

   The Schedule Backup dialog box appears.

   

3. If appropriate, change the task name and/or the SQL command.
   • The task name is generated automatically by SQL Enterprise Manager. The name defaults to Backup ¾ databasename. You can change it to another unique name.
   • The SQL command is the Transact-SQL text of the backup command. You can modify the command by typing valid DUMP statement syntax. For more information, see Customizing a Backup Command, later in this chapter.
4. Set the schedule.
   • To execute the backup immediately, choose Immediately. (The backup will run when you choose the OK button.)
   • To schedule the backup to run once, choose One Time, set a date in the On Date box, and set a time in the At Time box.
   • To schedule the backup to run at regular intervals, select recurring, choose the Change button, and complete the Task Schedule dialog box that appears. For more information, see the procedure that follows.
5. Choose OK.
   • If the option is set for Immediately, the backup executes.
   • If the option is set for One Time or Recurring, the scheduled backup task is created.

    To set a recurring backup schedule

1. Open the Schedule Backup dialog box as described in the previous procedure, select the Recurring option, and choose the Change button.

   The Task Schedule dialog box appears.

   

2. Set a frequency.
   • Daily

     If you select the Daily option, then the Daily frame appears in the dialog box. Enter the number of days in the recurrence cycle. The default is Every 1 Day(s), indicating that the recurring task will be executed each day.

     For example, if you enter 3, the recurring task is executed every third day.

   • Weekly

     If you select the Weekly option, then the Weekly frame appears in the dialog box. Enter the number of weeks in the recurrence cycle, and select the day(s). The default is Every 1 Week(s), on Sunday.

     For example, if you enter 2 and select Friday, the backup is run on Friday of every second week.

   • Monthly

     If you select the Monthly option, then the Monthly frame appears in the dialog box. Complete the choices in this frame.

     For example, if you enter Day 1 of Every 1 Month(s), the backup is run on the first day of every month.

3. Under Daily Frequency, specify the frequency with which the task will occur.

   On days that the backup runs, the time(s) it runs will run based on this setting. This can be either once a day at a specified time, or repeated at a specified interval within a specified range of hours.

4. Under Duration, set a start date and an end date.
   • Start Date is the first day on which the backup will be run.
   • End Date is the last day on which the backup will be run. (This option does not apply if No End Date is selected.)
   • No End Date sets an unlimited duration.
5. Choose OK.

   The recurring schedule is set. The Schedule Backup dialog box returns.

You can also schedule a backup from the Task Scheduling window. For more information, see Chapter 16, Scheduling Tasks. Also see Monitoring, Managing or Canceling a Scheduled Backup, later in this chapter.