October 20, 2010 at 12:36 am
Hi.
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,
Fran
October 20, 2010 at 2:15 am
Are you doing diff backups? If not, it's not so critical to use the copy_only option
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
October 22, 2010 at 4:42 am
Since I 'm not doing differentials backups, hasn't this option any effect?
Thank you!
October 22, 2010 at 8:24 am
Correct. No full backup (with or without copy_only) breaks the log chain.
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
October 22, 2010 at 1:28 pm
GilaMonster (10/22/2010)
Correct. No full backup (with or without copy_only) breaks the log chain.
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.
October 22, 2010 at 10:35 pm
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?
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.
According to Microsoft, you should backup log after the Full backup C to establish a new log chain.
Could you please provide the URL to the article? I would like to see the context the recommendation was made in.
Thanks.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
October 22, 2010 at 11:32 pm
Full 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.
October 22, 2010 at 11:35 pm
If 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.
October 23, 2010 at 3:11 am
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
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
December 4, 2012 at 3:43 am
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?
December 4, 2012 at 3:47 am
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.
December 4, 2012 at 3:53 am
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
December 6, 2012 at 4:24 am
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 🙂
-----------------------------------------------------------------------------
संकेत कोकणे
December 6, 2012 at 5:23 am
sanket kokane (12/6/2012)
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply