Simple question

At what point is a differential backup considered transactionally consistent?

Is it the creation date of the DIFF, or the time at which it completes?

That is if I restore it what transactions can I expect to have been committed? Those that completed before the time the DIFF started, or those that completed during the DIFF, that is the finished time?


Generally speaking a differential works very similarly to a full backup, except that only the changed extents in the DCM are backed up. Differential backups still contain a portion of the log needed to be transactionally consistent. This will differ for every environment and is not "known" at the time of backup. You can use the RESTORE HEADERONLY of the differential to check LSNs that are included and covered. When you think the transaction finished and when SQL Server wrote the logs that may or may not be flushed to disk when the checkpoint ran before the differential backup may or may not be included.

|improve this answer|||||
  • So if a DIFF takes 3 hours, and during those 3 hours 100 transactions were committed to the log, the DIFF will contain a large proportion of those transactions? That is it won't contain 0 of those 100 transactions. So the transactionally consistent timestamp is closer to the DIFF finish time that its start time? – user2321 Jul 6 '14 at 19:00
  • 1
    The differential probably won't include all of your new transactions. The differential backup works with changed extents and not necessarily transactions such as the log does. There is a chance most of them will be included, but again, there is no way to 100% say yes or no except for checking the LSNs. To answer your comment question, it will (that long running) most likely be closer to the finish time, again, depending on a bunch of variables. – Sean Gallardy Jul 6 '14 at 19:32
  • Thanks Sean. Would a good way to put it be, the diff starts by looking at all the changed extents and gets a list of extents to backup. It then works through backing up those extents. If transactions that were active and complete before the diff finishes affect those extents, the bits of the log relevant to those extents are backed up? So it only backs up extents that are marked as changed when the DIFF started, but they are transactionally consistent at the point in time when the DIFF completes? Extents modified between the start and end time are ignored (unless they are required for TC) – user2321 Jul 6 '14 at 20:33
  • I can't say exactly what the algorithm is (someone with more internal knowledge of this might be able to respond) but my educated guess would be it reads from the DCM in extent order, backing up those extents and any log needed for them until all of the DCMs have been processed. This is why I'm saying it may or may not as a DCM page exists every 4GB. I can't say what order they are read, serially or parallel, etc, but I would guess the DCMs aren't frozen for the operation. What you have would be a good way to look at it, I'm just not sure it works exactly like that. – Sean Gallardy Jul 6 '14 at 21:01
  • Thanks Sean you have been very helpful. This article by Paul Randall seems to help my understanding. sqlskills.com/blogs/paul/… It is very similar to what I say I think. – user2321 Jul 6 '14 at 21:43

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.