Full Backups
-
Comments posted to this topic are about the item Full Backups
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
-
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
Author - SQL Server T-SQL Recipes
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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
+1
I think, there should be transaction 1 commiting before Full backup reading ends.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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 🙂
-
WayneS (7/19/2012)
see http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx for more about the UNDO portion of recoveryAnd see another brilliant Paul Randal's article for the detailed explanation about the subject of the QotD: Understanding SQL Server Backups
-
I like the intent of the question, but alas poorly executed.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
+1
-
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
-
This was removed by the editor as SPAM
-
Koen Verbeeck (7/20/2012)
I like the intent of the question, but alas poorly executed.Agreed.
-
+3
But someone from the moderator's team should have checked this before posting 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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 ?
-
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
[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life! -
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.
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply