Pop Quiz: Restore a Database to the Point in Time when a Full Backup Started?

@James - I think the simple answer is no :-).

A backup is an online operation, thank goodness, so by necessity we have to assume the data pages are changing as the backup runs. It follows that the resulting backup file must contain "old" data pages and "newer" data pages in it, that were copied into the file at different times and represent different states. Different times = different log sequence numbers in SQL Server. In order to then restore to a consistent state, the data pages across the whole database, at the end of the restore process, have to be somehow resolved to the same LSN/moment in time. In order to do that, a portion of the log is also copied into the full backup file, and that slice of log is used to perform a part of the recovery process, which modifies any "old" pages in the backup file, which were written early in the backup process, bringing them up to the same instant of time as the "newest" page - the time of the completion of the backup.

Another way to look at it, if I even understand this right: the log, technically called "write-ahead log" - has to contain records as of an equal or later time than the latest page of data, in order for it to be possible to recover to a valid state. If there were data pages that are newer than the available log records, one could not know how to handle them, because the log would have no record about how they came into that state. It would be as if they came from the "future."

That is, the max LSN in the log file must be >= the max LSN represented on any data page in the whole database. Because the data is changing while a full backup executes, one has to assume that the maximum LSN represented in all the data pages in the backup file is the LSN exactly at the moment of the end of the backup execution. That means the log used to recover the database has to also run up to that moment. Hence it's impossible to recover to any state prior to the end of the backup, because the data file would contain mystery "future" pages, impossible to resolve.

Once the full backup is restored, however, it's in a consistent state as of the last moment of the backup execution and, going forward from that point, log backups can be restored up to any specified point in time - because the log is always "ahead" of the entire set of data pages and can accurately reproduce all changes to any piece of data.

There's good info about this here:

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/10/26/fuzzy-backups-and-restore-database-with-stopat.aspx

and

http://connect.microsoft.com/SQLServer/feedback/details/613644/restore-database-command-with-stopat-does-not-serve-the-actual-purpose

Hey Andrew - good one. But for a DBA? Actually we had one of our integration people drill a DBA candidate about a whole external load-balanced web service issue similar to that. He did well, luckily, but I kind of felt like it was a long way from DBA-land :-)