I have a database that automatically takes full backup weekly, differential backup daily, and transaction log backup every 5 minutes. The full backup takes about half an hour so its runtime often overlaps with the differential backup and any transaction log backups happens during.

What happens to the data modified during each types of backup? Would they be included in the backup or the backup contains only data up to the point of BackupStartDate or other attributes in the backup? I want to be able to calculate when trying to restore to a point of time.

If the Diff backup occurs after a full backup process has started and finishes after the full backup has finished, would it base on the last full backup or this one?

I haven't found a authoratative explanation on the web. Appreciate if anyone can point me to the right direction.

  • 1
    This post should answer at least part of your question.
    – Ronaldo
    Aug 5, 2020 at 2:17
  • You ask a few questions in here... The linked question above addresses the main question in your title. Regarding diffs, there's an important concept of the "Differential base". This is the full backup upon which the Diff is base. The corresponding LSN metadata is all included in the backup header & msdb for determining how to chain together full, diff, and logs.
    – AMtwo
    Aug 5, 2020 at 2:26
  • @AMtwo I understand I can examine the LSN of each individual backups to chain them up but I just want to know if the time of backup operations guarantees the order of the LSN so that I can judge it only by the start/finishing time which I could determine just by looking at the file creation time and such attributes without looking into the header. Also thanks for the good read. Aug 5, 2020 at 2:49
  • 1
    You can use time to estimate the order in which you can apply backups, but if you use that as the only method, you will have problems where you chain files inappropriately. The only way to know is to look at LSNs in the metadata.
    – AMtwo
    Aug 5, 2020 at 12:45


Browse other questions tagged or ask your own question.