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

3 Answers 3

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

Not sure this has really been answered, at least not without decoding all the posts and comments.

So, the key difference between Full and Copy-only backups if whether or not the LSN (Log sequence number), and specifically the DatabaseBackupLSN is updated.

When you take a Full backup, the DatabaseBackupLSN is updated. After taking the full backup, if you take a Differential backup that backup has a DatabaseBackupLSN which matches that of the Full backup, and therefore SQL can link the two together (eg it knows from those LSNs that the diff followed the full).

The problem comes when you have scheduled backups running, so you have an initial Full backup, and then a sequence of Differentials. If you manually take a Full backup it will update the LSN, and then from that point onwards each Differential backup you take via scheduled backup will reference that new LSN not the original one. In the event of needing to restore, you could restore your scheduled Full backup, but when you tried to restore any of the Differential backups taken after the manual job you'd find it would fail as the LSNs no longer match.

With a Copy-only backup it doesn't touch the DatabaseBackupLSN, and as such doesn't break that chain of backups.

There's a good description of the issue, as well as why so many people misunderstand it here http://sqlmag.com/blog/breaking-backup-chain-redux-or-eating-crow which includes good visual guides like this one :

SQLmag image - Full Backup v Copy_Only backups

and for a good explanation of the four different LSNs and how they're used have a look at http://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/

share|improve this answer

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.