Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Full Backups Expand / Collapse
Author
Message
Posted Sunday, July 29, 2012 4:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 23, 2013 12:51 AM
Points: 56, Visits: 53
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?????????

Søren

Post #1336980
Posted Saturday, August 4, 2012 2:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:46 PM
Points: 5,998, Visits: 8,261
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)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1340217
Posted Sunday, August 5, 2012 8:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 23, 2013 12:51 AM
Points: 56, Visits: 53
Hi again,
Thanks for your reply. I agree with you that in the scenario I describe, then the transaction will be in the backup.

That also leads to that the question needs to have the assumption added that a backup takes less than 60 seconds. If it takes more than 60 seconds, then the answer is wrong.

Soren
Post #1340277
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse