1

I am new in DBA and while learning different types of backup, one question came into my mind. So the question is, what will be inside the last differential backup, just the sum of the previous differential backups or the sum of previous backups plus t-log backups? Let's assume I have the following backups:

full_backup_2018_05_09_000000.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
diff_backup_2018_05_09_006000.bak
tran_backup_2018_05_09_006500.trn
tran_backup_2018_05_09_007000.trn
tran_backup_2018_05_09_007500.trn
diff_backup_2018_05_09_008000.bak
5

I'd definitely recommend that you read A SQL Server DBA myth a day: (30/30) backup myths from SQL Server expert Paul Randal for some really good information about SQL Server backups.

To answer your immediate question, the lastest differential backup (diff_backup_2018_05_09_008000.bak) in your question would contain all changed pages since the last full (non COPY_ONLY) backup (full_backup_2018_05_09_000000.bak) **PLUS ** enough of the transaction log to be able to recover the database to a transactionally-consistent view of the database at the time the data-reading portion of the backup finished. Transaction log backups would not affect what pages are included in full or differential backups

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.