<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Gail Shaw
»
Managing Transaction Logs
122 posts, Page 12 of 13
««
«
9
10
11
12
13
»»
Managing Transaction Logs
Rate Topic
Display Mode
Topic Options
Author
Message
SQLPhil
SQLPhil
Posted Thursday, January 12, 2012 3:48 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:35 PM
Points: 1,413,
Visits: 663
Echoing everyone else's comments - great article!
However, I was all prepared to query your statement in the article:
It is important to note that while a full or differential backup starts a log chain, full and differential backups don't break the log chain
My understanding was always that performing a Full backup
would
break the log chain, and if you wanted to restore to a given point using your log backups, you would have to restore from the last full backup. That's what I thought copy_only backups were introduced for, to ensure an ad-hoc full backup would not disrupt the log chain.
However, having now tested it against a 2008 R2 instance sure enough taking a full backup does
not
break the log chain, so I have learned something new today! Thank you!
But having said that, it begs the question what is the point of copy_only backups now?
Post #1234616
GilaMonster
GilaMonster
Posted Thursday, January 12, 2012 4:16 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:47 PM
Points: 38,096,
Visits: 30,391
SQLPhil (1/12/2012)
But having said that, it begs the question what is the point of copy_only backups now?
This:
http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/
You wouldn't be the first to argue with me about full backups breaking the log chain, it's an (unfortunately) commonly held belief. They've never broken the log chain though.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1234643
SQLPhil
SQLPhil
Posted Thursday, January 12, 2012 6:24 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:35 PM
Points: 1,413,
Visits: 663
Thank you very much Gail for clarifying all of this (and for the excellent article on COPY_ONLY). You have certainly added a lot of clarity to my understanding of backups and restores.
Speaking amongst my fellow DBA colleagues we were all under the (misguided) understanding that full backups broke the log chain. In fact, even in some training courses we've been told that this has been the case. It'll be nice to have the opportunity to turn round and inform the instructor for once!
Post #1234715
GilaMonster
GilaMonster
Posted Thursday, January 12, 2012 6:43 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:47 PM
Points: 38,096,
Visits: 30,391
SQLPhil (1/12/2012)
It'll be nice to have the opportunity to turn round and inform the instructor for once!
Just be polite if you do. Speaking as a occasional trainer and presenter, there's nothing worse than a student who thinks he's right and you're wrong and makes a public issue out of it.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1234750
ALZDBA
ALZDBA
Posted Thursday, January 12, 2012 6:55 AM
SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 2:13 PM
Points: 6,866,
Visits: 8,071
GilaMonster (1/12/2012)
...Speaking as a
occasional
trainer and presenter..
And a darned good one too, may I say !
Johan
Jul 13
Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere
-
How to post Performance Problems
-
How to post data/code to get the best help
-
How to prevent a sore throat after hours of presenting ppt ?
"press F1 for solution", "press
shift
+F1 for urgent solution"
Need a bit of Powershell? How about
this
Who am I ?
Sometimes this is me
but
most of the time this is me
Post #1234769
quackhandle1975
quackhandle1975
Posted Thursday, February 16, 2012 7:19 AM
SSCommitted
Group: General Forum Members
Last Login: Tuesday, May 28, 2013 9:41 AM
Points: 1,622,
Visits: 592
Great article, company I contract for are constantly changing the recovery model and shrinking the log then changing the RM back - have presented them with the URL for this, fingers crossed they will see sense!
qh
SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1253121
best_yunus
best_yunus
Posted Saturday, May 12, 2012 10:18 PM
Grasshopper
Group: General Forum Members
Last Login: Saturday, June 08, 2013 6:32 PM
Points: 10,
Visits: 53
Can we do point in time recovery in Bulk loged recovery model?
If yes then how?
Post #1299211
Jeff Moden
Jeff Moden
Posted Sunday, May 13, 2012 12:15 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112,
Visits: 27,038
best_yunus (5/12/2012)
Can we do point in time recovery in Bulk loged recovery model?
If yes then how?
Yes but only if there were no minimally logged transactions in the final log file that you want to recover up to the point in time.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
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/
Post #1299227
best_yunus
best_yunus
Posted Sunday, May 13, 2012 1:14 PM
Grasshopper
Group: General Forum Members
Last Login: Saturday, June 08, 2013 6:32 PM
Points: 10,
Visits: 53
Now i got...
Thanks
Post #1299287
GilaMonster
GilaMonster
Posted Monday, May 14, 2012 10:57 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:47 PM
Points: 38,096,
Visits: 30,391
Take a read through this:
http://www.sqlservercentral.com/articles/Recovery+models/89664/
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1299733
« Prev Topic
|
Next Topic »
122 posts, Page 12 of 13
««
«
9
10
11
12
13
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
