Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Create a policy to avoid backups withou copy_only option Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 3:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 8:13 AM
Points: 1, Visits: 90
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
Posted Tuesday, December 4, 2012 3:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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
Posted Tuesday, December 4, 2012 3:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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
Posted Thursday, December 6, 2012 4:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:48 AM
Points: 323, Visits: 986
[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
Posted Thursday, December 6, 2012 5:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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
Posted Wednesday, December 12, 2012 12:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:05 AM
Points: 47, Visits: 236
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
Posted Wednesday, December 12, 2012 1:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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
Posted Wednesday, December 12, 2012 1:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 »

Add to briefcase ««12

Permissions Expand / Collapse