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

  • 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 '15 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 '15 at 10:57
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.

  • 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 '15 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. – Shawn Melton Nov 22 '15 at 11:18

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.