Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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)
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!
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
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.
