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?
Best Answer
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):
If you want to pick apart what he means by "effectively" here, you have to understand the steps a full backup goes through:
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:
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.