patternsqlMinor
Why use multiple full & differential backups when I can use transaction log backups?
Viewed 0 times
whyfullbackupscanlogdifferentialmultiplewhentransactionuse
Problem
I'm new to DB backup. I just learned about backup types from this question(Link). After I read
For example, I have backups like this:
What's the difference between this and using one full backup and transaction log backups like this?
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
...Solution
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
VS
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.
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.trnVS
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.trnUtilizing 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.
Code Snippets
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.trnfull_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.trnContext
StackExchange Database Administrators Q#206334, answer score: 2
Revisions (0)
No revisions yet.