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 Thursday, July 19, 2012 8:42 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: Monday, October 27, 2014 1:09 PM
Points: 3,467, Visits: 1,821
Comments posted to this topic are about the item Full Backups

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 #1332665
Posted Thursday, July 19, 2012 9:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 5,365, Visits: 8,964
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.

Edit: see http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx for more about the UNDO portion of recovery.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1332676
Posted Thursday, July 19, 2012 9:54 PM


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
+1

I think, there should be transaction 1 commiting before Full backup reading ends.


~ 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 #1332678
Posted Thursday, July 19, 2012 10:00 PM


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
Further to what Wayne has written:
In order to use this full back-up to restore the datbase, we also need to apply the transaction log on top of the restore. This is required for all the transactions that were committed after the full backup reading ends.


~ 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 #1332680
Posted Thursday, July 19, 2012 10:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:44 PM
Points: 50, Visits: 138
Not a good question: because by your reasoning T1 would also be rolled back as well therefore
The changes from which transactions will be contained within the full backup?

All of them :)
Post #1332689
Posted Thursday, July 19, 2012 11:34 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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
WayneS (7/19/2012)
see http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx for more about the UNDO portion of recovery

And see another brilliant Paul Randal's article for the detailed explanation about the subject of the QotD: Understanding SQL Server Backups
Post #1332697
Posted Friday, July 20, 2012 12:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 13,362, Visits: 11,139
I like the intent of the question, but alas poorly executed.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1332719
Posted Friday, July 20, 2012 1:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 9:57 AM
Points: 90, Visits: 85
+1
Post #1332746
Posted Friday, July 20, 2012 1:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
I thought I had 2 Choices:

12:18 AM - Full backup data reading of QotDDatabase ends
12:19 AM - Transaction 4 on QotDDatabase commits
12:19 AM - Full backup transaction log reading of QotDDatabase happens
12:20 AM - Full backup of QotDDatabase ends


12:18 AM - Full backup data reading of QotDDatabase ends
12:19 AM - Full backup transaction log reading of QotDDatabase happens
12:19 AM - Transaction 4 on QotDDatabase commits
12:20 AM - Full backup of QotDDatabase ends


Just two of the possible interpretations though...


Best Regards,
Chris Büttner
Post #1332756
Posted Friday, July 20, 2012 2:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 4,040, Visits: 5,352
Koen Verbeeck (7/20/2012)
I like the intent of the question, but alas poorly executed.

+2


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1332768
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse