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 ««12

Transaction Log Backups Expand / Collapse
Author
Message
Posted Tuesday, February 9, 2010 8:46 AM


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, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
Lynn Pettis (2/9/2010)
Good question. Easy if you understand recovery models and backup/restore processes.


Yeah. What he said. Full means complete, all in one.

Nice question.


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #862486
Posted Tuesday, February 9, 2010 9:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
Excellent question. Then I noticed who the author of it is, and realized... but of course!

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 #862548
Posted Tuesday, February 9, 2010 9:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 2, 2013 9:32 PM
Points: 249, Visits: 221
That wouldn't be enough to recover the database. If there was a long running transaction that started before the full backup did, only backing up the transaction log generated during the backup would not allow the transaction to be rolled back correctly during the restore operation


I was under the impression that at the start of a backup, a checkpoint is issued and a marker is placed in the trans log indicating that a database wide lock was obtained (to ensure there are no running transactions), this lock is then released and the backup is performed. At the end of the backup, another checkpoint is issued (another lock obtained and released) and the section of the transaction log between the two checkpoints is backed up. Has this changed between 2005 and 2008 or did I dream all that
Post #862915
Posted Tuesday, February 9, 2010 9:41 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:48 AM
Points: 2,040, Visits: 1,667
With all due respect Ted, you dreamed everything you described above It doesn't do any of that except the checkpoint at the start, and that's just to flush out as many dirty pages from the buffer pool as it can, so as to reduce the amount of REDO recovery to do during the restore sequence.

[Edit: btw, a checkpoint doesn't take locks either - see my blog post for details: How do checkpoints work and what gets logged. Whatever differing descriptions you may read elsewhere are unfortunately incorrect - this description is based on the code itself.]

Take a look at my backup article I referenced a few posts above which explains exactly what happens.

It's worked the same way since 7.0 up to 2008.

Thanks


Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #862924
Posted Wednesday, February 10, 2010 3:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 12:44 AM
Points: 490, Visits: 1,341
@Paul,

Great question as expected from you.

thanks.


Regards,
Raj

Strictlysql.blogspot.com
Post #863043
Posted Wednesday, February 10, 2010 10:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 9:58 PM
Points: 10, Visits: 113
got this wrong, I answered A. No transaction log...

Reason being is that the question mentioned... regardless of recovery model.... I thought that a database with a Simple Recovery model did not use a transaction log, and therefore not back up a transaction log (which would rule out all other answers as they involve backing up parts of the transaction log)
Post #863821
Posted Thursday, February 11, 2010 12:51 PM


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: Yesterday @ 12:08 PM
Points: 540, Visits: 230
Great question... got this one wrong but that's how we learn!

Post #864268
Posted Tuesday, March 30, 2010 8:54 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Never get tired of this sort of question - it is such an important topic.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #892878
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse