Home / server / Questions / 561197
Accepted
Josh
Asked: 2013-12-14 07:39:58 +0800 CST

Colliding/overlapping full and differential backups in SQL Server

  • 772

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

Thanks

sql-server

1 Answers

  1. Best Answer

    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.

    • 1