4

If Full backup is scheduled for every Sunday at 12 pm and differential back up is scheduled for for every 6 hours and transaction log backup is scheduled for every 15 minutes.

My problem is what is the use of differential backup every 6 hours if we are taking a transaction log backup every 15 minutes? Is it enough to have only full backup and transaction log backup only ?

Thank you

  • Is the question what you are asking in the description? It looks like you are saying you have diffs in between. – johnny Dec 18 '18 at 21:57
4

My problem is what is the use of differential backup every 6 hours if we are taking a transaction log backup every 15 minutes?

Differential backup is used to decrease the Recovery time object(RTO), it gives you facility of restoring backups quickly. Differential backup includes all the changes made since last full backup, or full backup which started the differential chain. Differential backup is not affected by log backup so in event of disaster you would have to restore(in your case)

  1. 12 PM Sunday full backup
  2. Differential backup taken before the disaster strike. Note this is just one diff backup you have to restore no matter how many you have taken because its cumulative
  3. The log backups after diff backup (in point 2) stopping just before the disaster struck.
| improve this answer | |
3

The use of the differential Backup is simple. Instead of restoring a huge amount of log backups in a row, you restore a full, a diff and a small amount of transaction log backups instead.

Yes you are correct, you don't need the differential Backup, logically. But your restore will be faster using full + diff + log backup compared to a full restore + multiple log restores!

| improve this answer | |
0

It depends on you restore time Differential backup helps you reduce the restore time Imagine that each of your logs takes half an hour to restore and imagine that your database fails on Saturday So you have to Restore the full backup of last Sunday + many log backups which will take so much time but If you have differential backup every 6 hours you will restore 1 full backup , One differential backup and less than 10 backups

So It will definitely help you if your log backup restores takes too much time and you want to decrease your restore time

| improve this answer | |
0

Assume the case where you make full backups once a week (on Sunday) and transaction log backups every 15 minutes. Then the database failure occurred on Saturday night and now you need to restore your database.

In such case, you need to to restore the last full backup that was made on Sunday plus all transaction log backups that you were made from Sunday to Saturday. It will take a lot of time!

So it is better do not exclude differential backups from your backup plan. If the failure occurs you will spend a lot less time to restore you database using the following scenario - the last full backup + the last differential backup + all transaction log backups which were made since the last differential backup.

| improve this answer | |

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.