SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction Log Backups


Transaction Log Backups

Author
Message
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1251 Visits: 1499
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

WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9924 Visits: 10573
Excellent question. Then I noticed who the author of it is, and realized... but of course!

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, 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

Ted Stryker
Ted Stryker
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 236
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 :-D
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3671 Visits: 1717
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
arr.nagaraj
arr.nagaraj
SSC Eights!
SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)

Group: General Forum Members
Points: 858 Visits: 1590
@Paul,

Great question as expected from you.

thanks.

Regards,
Raj

http://Strictlysql.blogspot.com
peterj-1001000
peterj-1001000
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 121
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)
Steve Vassallo
Steve Vassallo
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 244
Great question... got this one wrong but that's how we learn!
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15856 Visits: 11355
Never get tired of this sort of question - it is such an important topic.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search