Transaction Log Backups

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Paul Randal » Transaction Log Backups

18 posts, Page 2 of 2

««1 2

Transaction Log Backups

Rate Topic

Display Mode

Topic Options

Author

Message

Tom Garth

Posted Tuesday, February 09, 2010 8:46 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 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

WayneS

Posted Tuesday, February 09, 2010 9:37 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:08 PM
Points: 6,388, Visits: 8,289

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

Ted Stryker

Posted Tuesday, February 09, 2010 9:26 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 02, 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 BigGrin

Post #862915

Paul Randal

Posted Tuesday, February 09, 2010 9:41 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:19 AM
Points: 1,910, Visits: 1,606

With all due respect Ted, you dreamed everything you described above Smile

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

arr.nagaraj

Posted Wednesday, February 10, 2010 3:05 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:03 AM
Points: 483, Visits: 1,199

@Paul,

Great question as expected from you.

thanks.

Regards,
Raj

Strictlysql.blogspot.com

Post #863043

peterj-863281

Posted Wednesday, February 10, 2010 10:15 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:26 PM
Points: 10, Visits: 111

got this wrong, I answered A. No transaction log... Sad

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