SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Full Backups


Full Backups

Author
Message
nerosdk71
nerosdk71
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 57
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
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10767 Visits: 11966
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
nerosdk71
nerosdk71
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 57
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search