3

I'm new to DB backup. I just learned about backup types from this question(Link). After I read the differential backup is cumulative since the last full, I got curious if multiple full and differential backups are not mandatory.

For example, I have backups like this:

full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_000500.trn
tran_backup_2018_05_09_001000.trn
diff_backup_2018_05_09_001500.bak
tran_backup_2018_05_09_002000.trn
tran_backup_2018_05_09_002500.trn
diff_backup_2018_05_09_003000.bak
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
diff_backup_2018_05_09_004500.bak
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn
full_backup_2018_05_09_010000.bak
...

What's the difference between this and using one full backup and transaction log backups like this?

full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_000500.trn
tran_backup_2018_05_09_001000.trn
tran_backup_2018_05_09_001500.trn
tran_backup_2018_05_09_002000.trn
tran_backup_2018_05_09_002500.trn
tran_backup_2018_05_09_003000.trn
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
tran_backup_2018_05_09_004500.trn
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn
tran_backup_2018_05_09_010000.trn
...
Improve this question
2
  • 1
    То restore DB state on some time point You must restore: last full before tp + every diff between last full and tp + every tran between last diff and tp. So diff backups decrease the total amount of backups to restore.
    – Akina
    May 10, 2018 at 9:14
  • In terms of managing your transaction log size/growth, there is no difference between the two approaches (as they both include transaction log backups) but they do differ in terms of RPO as described in the answers.
    – Peter
    May 10, 2018 at 9:30

3 Answers 3

Reset to default
2

Recovery Time Objective

RTO describes the time taken to perform a full restore of the database to a given point in time.

Restoring a large number of transaction log files will normally be considerably slower than only restoring the most recent differential followed by your transaction logs. Therefore your RTO would be longer.

Fault tolerance

If you lose a single transaction log backup (or that backup becomes corrupted or compromised in some way), your second example would not tolerate that single file being unfit/unavailable for restore, if you were trying to restore to the latest point in time.

The first example you provided requires you to restore only the most recent differential backup and then every subsequent transaction log backup, if you were trying to restore to the latest point in time.

DR Strategy

Often a database can be so large that performing more than 1 full backup per week is difficult to manage, in that the backup may not complete during a period of low activity, therefore the relatively light-touch of differential backups can help. The choice of how/when to do backups in SQL Server is based entirely upon RTO/RPO.

Improve this answer
2
  • How about just one full backup with multiple differential and transaction log backups? Sounds like multiple full backups are not better than my first example in RTO. Is this correct?
    – user2652379
    May 10, 2018 at 9:59
  • @user2652379 potentially yes, but all depends on your hardware and processes. Differentials, for example, become larger the longer you go without performing a full backup.
    – Peter
    Jun 4, 2018 at 16:07
2

The difference in functionality is minimal - the difference in what happens when you come to actually do a restore is tremendous.

Log backups are sequential, so in the above scenario to restore to 00:55 you would need to restore

full_backup_2018_05_09_000000.bak
diff_backup_2018_05_09_004500.bak
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn

VS

full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_000500.trn
tran_backup_2018_05_09_001000.trn
tran_backup_2018_05_09_001500.trn
tran_backup_2018_05_09_002000.trn
tran_backup_2018_05_09_002500.trn
tran_backup_2018_05_09_003000.trn
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
tran_backup_2018_05_09_004500.trn
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn

Utilizing DIFFs and FULLS means you don't need to replay through all those log files, which can be time consuming and potentially put a huge dent into your RTO (Recovery Time Objective)

You have to ask yourself in the event of a disaster, or any recovery scenario, how long are you willing to wait for the database to become available again - this normally dictates what methods you can then use.

Alternative answer: If you're a masochist, use just log backups.

Improve this answer
1

The main objective of differential backup is to reduce your Recovery Time Object(RTO). In more simple terms in event of disaster you can quickly recovery database using differential backup.

Supposing you do not have inbuilt script to restore backups so restoring full and lot of transaction log backups after that is time consuming as compared to restoring full backup then latest differential backup (since it is cumulative) and few log backups after that.

In two scenarios which you have shown above in case of disaster you would have to restore

full_backup_2018_05_09_000000.bak
diff_backup_2018_05_09_004500.bak--latest diff backups
tran_backup_2018_05_09_005000.trn--log backups after latest diff backups
tran_backup_2018_05_09_005500.trn

while if you do not have diff backup you would have to restore like

full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_000500.trn
tran_backup_2018_05_09_001000.trn
tran_backup_2018_05_09_001500.trn
tran_backup_2018_05_09_002000.trn
tran_backup_2018_05_09_002500.trn
tran_backup_2018_05_09_003000.trn
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
tran_backup_2018_05_09_004500.trn
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn
tran_backup_2018_05_09_010000.trn

Additionally I would suggest you to read Backup Myth by Paul Randal.

Improve this answer

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.