InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 01:59:54 GMT20http://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspx[quote][b]Hemant.R (12/12/2012)[/b][hr]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 .[/quote]Full backups [b]never [/b]break the log chain (LSN sequence), no matter what options are specified.Wed, 12 Dec 2012 01:58:00 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspx[quote][b]Hemant.R (12/12/2012)[/b][hr]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 .[/quote]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 baseWed, 12 Dec 2012 01:45:42 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspxhi ,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 .Wed, 12 Dec 2012 00:46:09 GMTHemant.Rhttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspx[quote][b]sanket kokane (12/6/2012)[/b][hr][quote][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.[/quote]Yes Please :-)[/quote]google: Paul Randal backup mythThu, 06 Dec 2012 05:23:50 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspx[quote][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.[/quote]Yes Please :-)Thu, 06 Dec 2012 04:24:57 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspx[quote][b]bjopette (12/4/2012)[/b][hr]The following transaction logs will be based on the backup the developer made[/quote]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 [b]differential base[/b].Tue, 04 Dec 2012 03:53:17 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspxYes, 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.Tue, 04 Dec 2012 03:47:08 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspx[quote][b]GilaMonster (10/23/2010)[/b][hr][quote][b]Wildcat (10/22/2010)[/b][hr]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?[/quote]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)[quote]According to Microsoft, you should backup log after the Full backup C to establish a new log chain.[/quote]Link please. I need to write a scathing email to either the author or the MS documentation people if such an official recommendation exists[/quote]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?Tue, 04 Dec 2012 03:43:18 GMTbjopettehttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspx[quoteThe 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)[/quote]Sorry, after reading Microsoft document 3 times, I misunderstood the concept. :ermm:Thanks, Gail.Thu, 28 Oct 2010 11:54:18 GMTWildcathttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspx[quote][b]Wildcat (10/22/2010)[/b][hr]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?[/quote]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)[quote]According to Microsoft, you should backup log after the Full backup C to establish a new log chain.[/quote]Link please. I need to write a scathing email to either the author or the MS documentation people if such an official recommendation existsSat, 23 Oct 2010 03:11:09 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspxIf you have truncated the log, you will need to take a full OR diff backup to start a new log chain.another case, if ur db has never been backed up(full recovery) then until you take full backup a new log chain wont start. db behaves as if it were in simple recovery mode.Fri, 22 Oct 2010 23:35:00 GMTps.http://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspxFull Backups do not break transaction log chain.However they do reset differential backups. If you take full backup wth copy_only option, the differential backup will contain pages that were modified after the last full backup without copy_only option.Fri, 22 Oct 2010 23:32:26 GMTps.http://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspx[quote]For example, from FULL BAKCUP A to FULL BACKUP B, we have a log chain. 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?[/quote]No. You can make as many full backups as you want between full backup A and B, and you will not break the log chain.[quote]According to Microsoft, you should backup log after the Full backup C to establish a new log chain.[/quote]Could you please provide the URL to the article? I would like to see the context the recommendation was made in.Thanks.Fri, 22 Oct 2010 22:35:04 GMTRay Mondhttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspx[quote][b]GilaMonster (10/22/2010)[/b][hr]Correct. No full backup (with or without copy_only) breaks the log chain.[/quote]Gail, I am confused here.For example, from FULL BAKCUP A to FULL BACKUP B, we have a log chain. 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?According to Microsoft, you should backup log after the Full backup C to establish a new log chain.Fri, 22 Oct 2010 13:28:54 GMTWildcathttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspxCorrect. No full backup (with or without copy_only) breaks the log chain.Fri, 22 Oct 2010 08:24:05 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspxSince I 'm not doing differentials backups, hasn't this option any effect?Thank you!Fri, 22 Oct 2010 04:42:14 GMThillock666http://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspxAre you doing diff backups? If not, it's not so critical to use the copy_only optionWed, 20 Oct 2010 02:15:09 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1007482-357-1.aspxHi. We have several databases in full recovery model. Daily we make full backups, but sometimes we need take a full backup for development purposes. I would like guarantee that everyone "remember" check copy_only option. Can I create a policy, or a DDL trigger that avoid this mistake?Thanks in advance,FranWed, 20 Oct 2010 00:36:25 GMThillock666