Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I saw in this link that full backup does not truncate the log. So what is the difference between full backup and copy-only full backup?

For the log backup there is copy-only backup which prevent the log chain from breaking without truncating the log. So what is copy-only full backup?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

At the very least you need to consider differential backups. Unless copy-only on the full is used, your next diff backup will be off. Copy-Only Backups:

Copy-only full backups (all recovery models) A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base.

share|improve this answer
    
The only difference between full and full-copy is that full-copy does not break the differential chain. Neither of them breaks the log chain as neither of them truncates the log file. –  Greenstone Walker Jul 8 '13 at 21:12

When you have a full backup and a set of log file backups, a log chain is maintained using the LSN (Log Sequence Number). If you want to do a backup without breaking the log chain, then do a copy-only backup.

If you do not do a copy-only backup, the log chain is broken and the backup you take will be the latest full backup. This means that the previous log backups cannot be applied to the newly taken full backups. The log chain is mostly maintained for point in time recoveries or log shipping scenarios.

For example: say you have a backup scenario that takes full backups every 6 hours (midnight, 6 am, noon, 6 pm) and log backups every 15 minutes. A request comes in at 9 am to have a copy of your DB placed on a test server. You want to take the backup without breaking your log chain or disrupting your backup jobs. This is when a copy-only backup is taken. The copy only backup will not disrupt your regular backup sets.

share|improve this answer
1  
I dont think copy-only backup effects log chain.A copy-only full backup does not reset the differential base. That's the only difference.See these links sqlservercentral.com/Forums/Topic1471058-391-1.aspx?Update=1 and sqlinthewild.co.za/index.php/2011/03/08/… –  IT researcher Jul 8 '13 at 15:06
    
@ITresearcher, thanks for agreeing and the links. –  StanleyJohns Jul 8 '13 at 16:23
    
I didn't agree your answer. Both full backup and copyonly full backup don't break the log chain.Other than the 'not resetting differential base' a copy only full backup is exactly the same as a normal full backup in every way. See the forum link i have mentioned in my previous comment. –  IT researcher Jul 11 '13 at 6:06
1  
You didn't updated your answer. –  IT researcher Jul 16 '13 at 10:44
1  
-1 because copy only option with a full backup has nothing to do with LSN chains. Itresearcher pointed that out but you didn't update/delete your answer. –  Edward Dortland Jan 3 at 7:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.