<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
»
SQL Server 2005
»
Backups
»
Create a policy to avoid backups withou...
18 posts, Page 2 of 2
««
1
2
Create a policy to avoid backups withou copy_only option
Rate Topic
Display Mode
Topic Options
Author
Message
bjopette
bjopette
Posted Tuesday, December 04, 2012 3:43 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, February 27, 2013 3:37 AM
Points: 1,
Visits: 63
GilaMonster (10/23/2010)
Wildcat (10/22/2010)
If you issue a FULL backup C without COPY_ONLY between A and B, then, you break the previous log chain A to B.
Right?
Wrong.
Full backups do not and never have broken the log chain.
It's trivial to test and, if you want, I can find a blog post from the former program manager of the Storage Engine stating and proving this.
The only things that break the log chain are:
* Switch to simple recovery
* Backup log with nolog/truncate only
* Deleting a log backup (won't stop you taking log backups, will stop you recovering with them)
According to Microsoft, you should backup log after the Full backup C to establish a new log chain.
Link please.
I need to write a scathing email to either the author or the MS documentation people if such an official recommendation exists
How about this scenario? You have a system setup with a regular schedule, full backups daily and transaction log backups every 1h. One day a developer decides to take a manual backup not using COPY_ONLY, and for some reason decides not to keep the backup file and deletes it. The following transaction logs will be based on the backup the developer made, and unfortunately the server crashes the same day, before the next scheduled full backup happened. Would it be possible to restore the data using the previous full backup if the transaction logs between the previous full backup and the time of the crash, is kept?
Post #1392333
anthony.green
anthony.green
Posted Tuesday, December 04, 2012 3:47 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
Yes, full backups do not affect the transaction log chain, so you can do as many full backups you want a day and can still recover from any of them should you have the right log sequence.
Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1
&
Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger
Post #1392336
GilaMonster
GilaMonster
Posted Tuesday, December 04, 2012 3:53 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671,
Visits: 29,925
bjopette (12/4/2012)
The following transaction logs will be based on the backup the developer made
Log backups are not based on a specific full backup (except when it's the first full backup made), so in your scenario the developer could take as many full backups as he likes and it will have absolutely no effect whatsoever on your recovery path.
The only thing that copy_only on a full backup does is to not reset the
differential base
.
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 #1392341
sanket kokane
sanket kokane
Posted Thursday, December 06, 2012 4:24 AM
Old Hand
Group: General Forum Members
Last Login: Yesterday @ 10:50 PM
Points: 323,
Visits: 962
[b]GilaMonster (10/23/2010)
It's trivial to test and, if you want, I can find a blog post from the former program manager of the Storage Engine stating and proving this.
Yes Please
-----------------------------------------------------------------------------
संकेत कोकणे
Post #1393428
GilaMonster
GilaMonster
Posted Thursday, December 06, 2012 5:23 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671,
Visits: 29,925
sanket kokane (12/6/2012)
[b]GilaMonster (10/23/2010)
It's trivial to test and, if you want, I can find a blog post from the former program manager of the Storage Engine stating and proving this.
Yes Please
google: Paul Randal backup myth
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 #1393450
Hemant.R
Hemant.R
Posted Wednesday, December 12, 2012 12:46 AM
SSC Rookie
Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 45,
Visits: 175
hi ,
you can used copy_only option to take full backup. when transaction log backup job created for same database .
bcoz it will not break lsn squence of database .
Post #1395471
anthony.green
anthony.green
Posted Wednesday, December 12, 2012 1:45 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
Hemant.R (12/12/2012)
hi ,
you can used copy_only option to take full backup. when transaction log backup job created for same database .
bcoz it will not break lsn squence of database .
A FULL backup will not break the log chain LSN if you dont specify the COPY_ONLY option. The only time a log backup is dependent on the FULL backup is when it is the first ever FULL and log backup for the database, or if the database has been changed from Full to Simple to Full.
The only base a FULL backup resets is the differential base
Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1
&
Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger
Post #1395493
GilaMonster
GilaMonster
Posted Wednesday, December 12, 2012 1:58 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671,
Visits: 29,925
Hemant.R (12/12/2012)
hi ,
you can used copy_only option to take full backup. when transaction log backup job created for same database .
bcoz it will not break lsn squence of database .
Full backups
never
break the log chain (LSN sequence), no matter what options are specified.
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 #1395503
« Prev Topic
|
Next Topic »
18 posts, Page 2 of 2
««
1
2
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.
