Difference between Full backup and Copy-only full backup
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:
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.
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.
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
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.