34

I saw in the SQL Server Central thread Does a full backup truncate the log? that full backup does not truncate the log:

No. Neither Full or Differential backups truncate the transaction log. - Lynn Pettis
No - a full backup does not truncate the log. - Chad Crawford

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?

5 Answers 5

21

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.

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.

0
43

The key difference between Full and Copy-only backups is 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 in Breaking the Backup Chain – REDUX (Or Eating Crow) by Michael K. Campbell (archive with all images), which includes good visual guides like this one:

SQLmag image - Full Backup v Copy_Only backups

For a good explanation of the four different LSNs and how they're used, have a look at Understanding SQL Server Log Sequence Numbers for Backups by Simon Liew.

The way to avoid the issue is to not have more than one thing doing standard backups of a database. Any adhoc or secondary backups should be done with the copy-only option, see Copy-Only Backups (SQL Server) for full details, but essentially you use the "Copy Only Backup" option in SSMS, via T-SQL specify WITH COPY_ONLY in the command, or with PowerShell use the -CopyOnly parameter.

0
21

Assume that we have a database with scheduled backups. The full backup runs once in 24 hours at 00:00, also we have differential backups that run every 6 hours, and transaction log backups that run every hour. So, what if we need to make an extra full backup in the middle of the day, to restore another server? What shall we do in this case. Of course, we can make a full backup.

BACKUP DATABASE Test TO DISK = 'C:/Test.bak'

But when you make a backup of a database, there are some changes that influence how the following backups are going to be restored (differential backups and transaction log backups both influence how the restore operation will be). In this case, all following differential backups will be dependent on the last full backup. If the last full backup is lost the database restore is impossible. enter image description here

But how can we make a backup that will not affect the following processes of backup or restore for that database. This is where copy-only backups come into place.

BACKUP DATABASE Test TO DISK = 'C:\Test.bak' WITH COPY_ONLY

enter image description here

2
  • So there's nothing inherently bad about the "Full Backup without COPY_ONLY" in the first diagram. It's just now you can't lose that backup - it's not just a backup you can stick on a USB stick and ship off somewhere for testing - because it is part of the chain. The virtue of COPY_ONLY is to let SQL Server know it's a one-off backup that not to worry about it.
    – Ian Boyd
    Commented Dec 16, 2021 at 16:32
  • In the first diagram, withOUT COPY_ONLY, why don't the future differential backups work against the [new, withOUT COPY_ONLY] full backup? That is, I realize you couldn't use them with the previous entries in the line, but it seems like the purple and green would simply move down to the withOUT full backup in that case and continue diff backups successfully. In other words, why make those final differential backups at all if they can't be applied to something?
    – ruffin
    Commented Mar 25, 2022 at 20:48
1

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.

7
  • 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/… Commented Jul 8, 2013 at 15:06
  • 1
    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. Commented Jul 11, 2013 at 6:06
  • Lets say you have a full backup: FB1 and 3 log backups: LB1, LB2, LB3. Now do a manual full backup: FB2 (without copy_only). Wait for 3 more log backups: LB4, LB5, LB6. Now delete FB2. Can you restore FB1+LB1+LB2+LB3+LB4+LB5+LB6? Commented Jul 11, 2013 at 7:33
  • Yes i can restore. i took full backup(not copyonly) FB1 ,then log backup(LB1),then full backup(not copy only)FB2, then again log backup(LB2). then i restored in this sequence FB1+LB1+LB2. Restored properly and found all rows entered properly. Commented Jul 11, 2013 at 10:56
  • 2
    -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. Commented Jan 3, 2014 at 7:36
1

A full backup and copy only backup do not break the log chain. only if you do a tlog backup will there be an LSN mismatch.

1
  • This is entirely untrue and represents the most common misunderstandings about full and transaction log backups. Look at the answers above that correlate full backups and the differential backups that depend on them.
    – matty
    Commented Sep 7, 2023 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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