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