Backup cut-off time for Full, Differential, Log, File, Partial backups

Question

My understanding was that all backups contain data up to the time/point when backup operation was completed.

Administering a SQL Database Infrastructure - Exam Ref 70-764 says:

Full: This contains the entire contents of the database and any changes made to the database during the backup operation. Consequently, a full backup represents the database at the point in time when the backup operation finished.

(emphasis mine)

However, for differential backups, the book implies something different:

Differential: This contains only the differences between the last full database backup and the point in time when the differential backup operation was executed.

Is this correct? Or is this simply imprecise language? Does "executed" here mean the time the differential backup started or completed?

How about for other backup types (Log, Partial, File backup) the book doesn't say this exactly?

Answer 1

See this related question for the answer regarding full backups. The definitive technical source referenced there is a 2009 article by Paul Randal (who was a member of the Microsoft SQL dev team):

A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored. Even though it may take many hours for the backup process to run, you can still only restore the backup to a single point (effectively at the end of the backup, but I'll discuss exactly what that point is later in this article). A full backup does not allow recovery to any point in time while the backup was running.

If you want to pick apart what he means by "effectively" here, you have to understand the steps a full backup goes through:

1. Checkpoint the database and note the current log sequence number
2. Start reading the data files
3. Stop reading the data files, and note the current log sequence number
4. Read enough of the log file to capture transactions completed between steps 1 and 3.

A full backup contains the proper data to restore the database to the point between steps 3 and 4. And since step 4 typically completes quickly (unless there was tremendously high activity during the backup), this is effectively the end of the backup.

Regarding Differential backups, Randal (in the same article linked above) says:

A differential backup performs the same operations as a full backup, but only contains all the data that has changed or been added since the previous full backup.

He then goes through the detail of how exactly SQL identifies which pages have changed since the last full, but that only changes how steps 2-3 above operate, it doesn't change anything else. I have to assume that implies the same answer, that restoring a full then a differential backup will restore to a point very near the end of the differential backup, with the caveats we've described above.

I'll look for an authoritative source about log backups.

Sorry, I don't have any detail about file or partition backups, I would guess they operate similar to a full, just with a different set of data pages.

Answer 2

If I had to guess then i'd say when a differential backup starts it identifies the changed pages at that point and backups those extents. Anything else is most likely not backed up.

My reasoning is that in many organization changes are taking place 24x7 and that would essentially turn a diff into a full or leave bugs in the code where the backup would not stop