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 Friday, July 20, 2012 12:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,204, Visits: 15,649
There was a slight typo in the original question, and I mistakenly added another in fixing it. It should have been:

12:10 AM - Transaction 1 on QotDDatabase begins

12:11 AM - Transaction 2 on QotDDatabase begins

12:12 AM - Transaction 2 on QotDDatabase commits

12:13 AM - Transaction 1 on QotDDatabase commits

12:14 AM - Full backup data reading of QotDDatabase begins

12:15 AM - Transaction 3 on QotDDatabase begins

12:16 AM - Transaction 3 on QotDDatabase commits

12:18 AM - Full backup data reading of QotDDatabase ends

12:19 AM - Transaction 4 on QotDDatabase begins

12:20 AM - Full backup of QotDDatabase ends

12:21 AM - Transaction 4 on QotDDatabase commits

In this case, 1, 2, and 3 are included, 4 is not as it starts after the data reading portion of the backup ends. I'll award back points to people since it was confusing.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1333168
Posted Friday, July 20, 2012 12:39 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 3,969, Visits: 3,646
Miles Neale (7/20/2012)
I answered that only the answer of 2 was correct. All others had to be wrong since 1 never completes. I do not care about the rules of the entire backup process and all the conversation here about such, (which is great by the way and very educational). The answers were all predicated by the transactions listed in those answers. Only 2 was the most correct, it was the only one that was completely correct. To say that 1 ever was included in the backup is truly false, it had not committed at the completion of the backup so all answers that included 1 are false.

Someone needs to adjust the points on this one,

Yes, that was how I stumbled upon the "correct" answer. None of them seemed to be correct and then noticed 2 commits for transaction 2 and assumed ...
Post #1333170
Posted Friday, July 20, 2012 1:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:38 AM
Points: 23, Visits: 61
The question was which transaction changes will be contain in the full backup?

In my understanding all transaction changes until the point where backup finish are recorded in the backup file. Since transaction 4 committed after backup was finish therefore only the changes up to the point of backup finished from transaction 4 will be recorded.

Another story is when backup is used to restore database from backup file then ALL changes for transaction 4 contained in backup file will be rollback since transaction 4 did not have commit statement as other three transaction have.

Regards

Krystian

Post #1333188
Posted Friday, July 20, 2012 1:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,204, Visits: 15,649
krystian (7/20/2012)
The question was which transaction changes will be contain in the full backup?

In my understanding all transaction changes until the point where backup finish are recorded in the backup file. Since transaction 4 committed after backup was finish therefore only the changes up to the point of backup finished from transaction 4 will be recorded.



Incorrect. On a busy system, the full backup would never end and constantly have new log entries. The log up to the end of the data reading portion of the backup is included. After that point, just the log from the beginning of the backup to the end of the data reading portion is written to the backup file.

If you look at my post a few back, that is the most recent, corrected, timeline of events.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1333202
Posted Friday, July 20, 2012 1:49 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:36 PM
Points: 985, Visits: 1,831
Steve Jones - SSC Editor (7/20/2012)
There was a slight typo in the original question, and I mistakenly added another in fixing it. It should have been:

12:10 AM - Transaction 1 on QotDDatabase begins

12:11 AM - Transaction 2 on QotDDatabase begins

12:12 AM - Transaction 2 on QotDDatabase commits

12:13 AM - Transaction 1 on QotDDatabase commits

12:14 AM - Full backup data reading of QotDDatabase begins

12:15 AM - Transaction 3 on QotDDatabase begins

12:16 AM - Transaction 3 on QotDDatabase commits

12:18 AM - Full backup data reading of QotDDatabase ends

12:15 AM - Transaction 4 on QotDDatabase begins

12:20 AM - Full backup of QotDDatabase ends

12:21 AM - Transaction 4 on QotDDatabase commits

In this case, 1, 2, and 3 are included, 4 is not as it starts after the data reading portion of the backup ends. I'll award back points to people since it was confusing.


Sorry to be pedantic, Steve, to make sure -- you mean transaction 4 starts at 12:19, right?
Post #1333209
Posted Friday, July 20, 2012 1:53 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:36 PM
Points: 985, Visits: 1,831
Also, you have to consider that SQL will stop a backup even if there are open transactions. I know because we have had a rogue developer who was given too much access by the CIO at my last job who more than once left a transaction open by mistake for days, but no one caught it at first because he wasn't hitting recent data (he was fixing old data). But we didn't get backup failures or backups going longer than normal, so SQL Server obviously at some point ignores open transactions and finishes the backup.
Post #1333211
Posted Friday, July 20, 2012 2:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,204, Visits: 15,649
jeff.mason (7/20/2012)
Steve Jones - SSC Editor (7/20/2012)
There was a slight typo in the original question, and I mistakenly added another in fixing it. It should have been:

12:10 AM - Transaction 1 on QotDDatabase begins

12:11 AM - Transaction 2 on QotDDatabase begins

12:12 AM - Transaction 2 on QotDDatabase commits

12:13 AM - Transaction 1 on QotDDatabase commits

12:14 AM - Full backup data reading of QotDDatabase begins

12:15 AM - Transaction 3 on QotDDatabase begins

12:16 AM - Transaction 3 on QotDDatabase commits

12:17 AM - Full backup data reading of QotDDatabase ends

12:19 AM - Transaction 4 on QotDDatabase begins

12:20 AM - Full backup of QotDDatabase ends

12:21 AM - Transaction 4 on QotDDatabase commits

In this case, 1, 2, and 3 are included, 4 is not as it starts after the data reading portion of the backup ends. I'll award back points to people since it was confusing.


Sorry to be pedantic, Steve, to make sure -- you mean transaction 4 starts at 12:19, right?


Yes, this whole thing makes my head hurt. Too hard to edit this and not make a mistake somewhere.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1333238
Posted Friday, July 20, 2012 6:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 10:54 AM
Points: 56, Visits: 61
LOL

I say we throw the whole thing out like the question never existed, no one got it wrong and no one go it right. We'll just call it a free learning experience. :)
Post #1333294
Posted Monday, July 23, 2012 1:11 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:42 AM
Points: 1,442, Visits: 726
Ah, the benefit of turning up to this party a bit late.

Turned out to be a nice question for me on a sunny Monday morning!
Post #1333620
Posted Monday, July 23, 2012 9:04 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 3,788, Visits: 1,212
SQLPhil (7/23/2012)
Ah, the benefit of turning up to this party a bit late.

Turned out to be a nice question for me on a sunny Monday morning!

+1

Same case here.


"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.
Post #1333862
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse