nerosdk71 (7/29/2012)
Sorry to ask a stupid question, but what will the answer be if the make the assumption that the backup that starts at 12.20 takes 15 minutes to run, and the read from the datafile to the backup takes e.g. 12 minutes. that means that data read is finished after the commit of transaction 4.Will transaction 4, then be included or excluded in the full backup?????????
Not a stupid question at all. (Stupid questions don't exist, in my opinion, unless you count questions not asked out of fear for being considered stupid).
The short answer: Transaction 4 will be included, since it is committed before the full database backup finishes.
The long answer (and I'm not 100% sure here, as I am not a full-blown expert on backup internals) is that the data portion of the backup may contain a mix of pages with "pre-transaction 4" and "post-transaction 4" data, depending on whether the backup process read the pages before or after transaction 4 touches them. The log portion of the backup then contains the information to roll forward any changes from transaction 4. And if there was a transaction 5 that started before or during the backup but finished later, the log part of the backup will also contain the information to roll back changes from that transaction, so that data pages that were already changed before the backup process read them can be restored to their original (before transaction 5) state.
Some other quick comments:
sestell1 (7/20/2012)
Wow, interesting. I'd been under the false impression that the full backup used an internal snapshot to capture the state of the database at the time the backup started.
Maybe you are confusing backup with DBCC CHECKDB? Since (I believe) SQL Server 2005, DBCC CHECKDB does indeed use an internal database snapshot.
Steve Jones - SSC Editor (7/20/2012)Incorrect. On a busy system, the full backup would never end and constantly have new log entries.
For the record, that would only happen on a system that is so busy that the log grows too fast for the database process to catch up. On a normal, or even very, busy system, the database process will eventually catch up with the new log entries and finish.
(Just adding this because I think some readers might -incorrectly- interpret this remark as the backup process waiting for all transactions to commit or rollback)