6

I have a SQL SERVER DB that i want to backup via automated schedule (using GUI maintenance plan) . I have 2 choices when configuring a schedule :

1) do not bother about log backup , full backup and diffrencial bakcup event time , as if all the 3 backups occur on same time , it makes no difference .

2) make sure that log backup, full backup and differential backup never occur in same time by configuring carefully the time of each backup.

Below is the image describing the 2 choices (Schedule1 for choice 1 and chedule2 for choice 2 )

Do you know which one is the best ?

Edit : Sechdule1 may face some errors when backups are overlaping . Here the backups are of type log and differential hapening same time at 5:30 as the backup whatever the type is will be appended to the existing backup file. So the file was in use by log backup and differential failed . Even if no data was lost.

enter image description here

Improve this question
2
  • Why are you taking full and differential backups so frequently? The typical cadence is full backups semi-frequently (perhaps once a week), differentials slightly more frequently (perhaps once a day), and then log backups to support your RPO. Either way, taking a full backup once an hour seems very aggressive.
    – Ben Thul
    Nov 22, 2015 at 6:39
  • the schedule is for illustration purpose. the idea is to clarify that sometimes the backups will overlap if not scheduled properly .
    – Java Main
    Nov 22, 2015 at 10:57

1 Answer 1

Reset to default
4

First step: Understand how backups really work. The main things to understand:

  1. Log backups after SQL Server 2000 can be taken concurrently during full and differential backups. The only thing to remember is during that timeframe the log will not be cleared until that full or differential backup completes.
  2. Full and differential backups that collide will cause whichever backup started first to take precedence, the second backup will wait in line until prior backup is completed. So if you have a FULL backup running, and a DIFF backup starts, that DIFF backup will wait until the FULL is complete. I do believe it should be the same for the opposite as well.

Second Step: Whichever backup plan you choose ensure it meets the recovery requirements for the application AND/OR the business.

So while either schedule will work the schedule 2 will likely lessen the amount of IO activity that is caused when the backups overlap. It would based on the size of the database and how much activity SQL Server has to backup during the differential and log backups. Depending on how you setup schedule 1 you could end up with all three backups trying to occur at the same time frame.

Improve this answer
2
  • Thank you for the clarity i will complete the reading on backups . I also had same feeling regarding the overlap specially when the DB is very big . But someone can ask you why are you doing differential and log while you are taking full backup? Or similarly can ask you why are doing log while doing differential ?
    – Java Main
    Nov 22, 2015 at 11:00
  • You perform the backups that are required to meet your recovery needs. In larger databases you will tend to perform full backups less frequently strictly from the time it takes to do them, as it will be considerable IO while it is running, not because it is a blocking event or anything.
    – user507
    Nov 22, 2015 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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