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


Create a policy to avoid backups withou copy_only option


Create a policy to avoid backups withou copy_only option

Author
Message
bjopette
bjopette
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 92
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?
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25412 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232264 Visits: 46356
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, MVP, M.Sc (Comp Sci)
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


sanket kokane
sanket kokane
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 1022
[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 :-)

-----------------------------------------------------------------------------
संकेत कोकणे
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232264 Visits: 46356
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, MVP, M.Sc (Comp Sci)
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


Hemant.R
Hemant.R
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 240
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 .
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25412 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232264 Visits: 46356
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, MVP, M.Sc (Comp Sci)
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


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