I am trying to set up a monthly full backup and a daily differential. The full backup is currently on the 1st, but I might change it to the first Saturday. I need to retain full backups for a few months (multiple sets).

My question is: on the first of the month how can I prevent the differential backup from running? I don't want them both going at the same time, or the differential to run based on a previous full...if that makes sense.

Is there a way to exempt certain days of the week or month from the differential schedule? I wasn't able to find any way to do that in the Maintenance Plan Wizard...

This is on SQL Server 2012 Enterprise



If you want that proposed schedule, your best bet is to run your backups from Windows Task Scheduler. The "monthly" option allows you to pick the days of the month using a set of check-boxes.

Another option is to simply run differentials after the time you run your full backup; pick a delay that suits your situation.

And, yes, on Sql2012, a full backup will block a concurrent differential backup. I just kicked off a full backup of a catalog (that takes about a minute to run on my SSD), then in another window kicked off a differential on the same catalog; the differential waited for the full backup to complete.

  • Oh interesting, so it does actually block, not cancel. So basically you had a full backup, and "immediately" after an "empty" differential backup. Is that correct?
    – Josh
    Dec 16 '13 at 13:44
  • Yes. And as part of my backup sproc, it also runs "RESTORE VERIFYONLY" against the backup file that was created, so it's as close to "good" as you can get without actually doing a restore.
    – Granger
    Dec 16 '13 at 23:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.