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 2:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:02 AM
Points: 2,007, Visits: 3,427
Koen Verbeeck (7/20/2012)
I like the intent of the question, but alas poorly executed.


Agreed.
Post #1332782
Posted Friday, July 20, 2012 4:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
+3

But someone from the moderator's team should have checked this before posting :)


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1332833
Posted Friday, July 20, 2012 5:08 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: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
This implies two things:
1. Any open transaction before full DB backup READ but not committed or rollbacked will be included in the full backup. [It sounds strange to me as ideally backups should not have consider any open transaction]
2. New transaction which is opened after full DB backup READ starts, which is also not committed or rolled back even after full DB backup READ ends, should not be included in the backup. [Which looks ok to me]

Could someone help me with the first point ?
Post #1332860
Posted Friday, July 20, 2012 6:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:02 AM
Points: 1,258, Visits: 13,556
WayneS (7/19/2012)
The full backup will also contain the uncommitted transactions from transaction 4 at the time that the backup finishes. If a restore is necessary, the UNDO portion of the restore will roll back those transactions. Therefore, the full backup will contain transactions from all four of the transactions.

A small error in the question: transaction 1 never is committed, and transaction 2 is committed twice (which can't happen). Obviously, one of those transaction 2 commits is for transaction 1.


+1



rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1332908
Posted Friday, July 20, 2012 6:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 1,361, Visits: 2,366
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. I'm intrigued that that is not the case, and changes to pages made after the backup has read the page are captured from the log at the end of the backup process. This seems a bit awkward to me, as it makes the point in time of the backup a bit fuzzy depending on how long it takes to process.
Post #1332920
Posted Friday, July 20, 2012 7:15 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 5:21 PM
Points: 5,877, Visits: 13,005
A full backup contains enough of the transaction log so the database can be made consistent on restore. The way the question is worded transaction 4 commits before the end of the backup so would be included in the backup.

---------------------------------------------------------------------

Post #1332947
Posted Friday, July 20, 2012 7:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:29 AM
Points: 83, Visits: 740
Deleted by author (I neglected to read the first post before posting).
Post #1332959
Posted Friday, July 20, 2012 7:59 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: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
Koen Verbeeck (7/20/2012)
I like the intent of the question, but alas poorly executed.


Agreed +1

the hard part was with the execution being done so poorly, making sure of the intent of the question became much harder.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1332983
Posted Friday, July 20, 2012 8:22 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: Yesterday @ 2:22 PM
Points: 3,466, Visits: 1,819
I should apologize. I read the silly thing several times unfortunately I missed the fact that I failed to commit transaction 1 at all. To much copy and paste I guess. Obviously I need to read it a few more times next time.

Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
Post #1333003
Posted Friday, July 20, 2012 8:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:37 PM
Points: 589, Visits: 2,437
I just focussed on the last three lines and counted the 'begins' and 'commits': 4 begin, 3 commit and only the transaction 4 had been commit after the backup reading process...
12:18 AM - Full backup data reading of QotDDatabase ends
12:19 AM - Transaction 4 on QotDDatabase commits
12:20 AM - Full backup of QotDDatabase ends
Nice and simple logic...

Thanks!
Post #1333007
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse