Conditional maintenance plan for backups

Question

I've set 2 schedules on a maintenance plan (SQL Server) for backups.

One of the schedules is set to run each 1 hour for a full database backup, and the other is set to run each 20 minutes for a differential backup. The problem is that they will execute at the same time when the first schedule runs.

How can I set the differential backup to avoid running at time X:00 ?

Current setup:

• 00:00 - Full backup + Diff backup (Problem)
• 00:20 - Diff backup
• 00:40 - Diff backup
• 01:00 - Full backup + Diff backup (Problem)

I want it to execute like this:

• 00:00 - Full backup only
• 00:20 - Diff backup
• 00:40 - Diff backup
• 01:00 - Full backup only

Answer 1

You could create 3 Maintenance plans:

1. One to do the Full Backup hourly Starting at eg. 08:00,
2. another to do the 1st Differential Backup repeating hourly starting at eg. 08:20
3. lastly another to do the 2nd Differential hourly starting at eg. 08:40.

As these can then repeat hourly, you'll get the 3 backups per hour. To make it easier, put them into the same backup folder and include description in each backup name (eg. Full_, FirstDiff_, SecondDiff_).

Answer 2

You can use the Subplans feature

Use the Subplan Schedule option to set the schedule for the task(s). The option will open the standard New Job Schedule dialog, as used by the SQL Server Jobs feature. Additionally, use the Add Subplan option to add subplans to the maintenance plan in order to group maintenance tasks per schedule time