17

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?

14

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.

23

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, 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.

  • 1
    To add: Practically COPY ONLY allows making a backup for non backup purposes. For a customer, backups are done autoamtically to an enterprise backup system - getting a restore is PAINFULL, particularly getting one to a different environment (paperwork, doing during the day). COPY ONLY allows me to make a copy WITHOUT interfering with the backup managed by enterprise backup and then restore it to a test environment. – TomTom Sep 24 '17 at 18:36
12

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

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.

  • 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
  • 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. – IT researcher Jul 11 '13 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? – StanleyJohns Jul 11 '13 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. – IT researcher Jul 11 '13 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. – Edward Dortland Jan 3 '14 at 7:36
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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