Copy-Only Backup
Published: 3rd May 2013
This is a crucial addition to SQL Server which is invaluable at times. It basically takes a snapshot of the database rather than an actual backup. Therefore it can be used to restore a database and to provide a copy, but crucially it does not break the backup chain.

What is a backup chain? Well, when you have a database in Full Recovery mode you need a constant chain of backups in order to have the ability to restore the database correctly.

For example, we have the following scenario:

Nightly full backup
6 hourly differential backup
Hourly transactional backup

Let’s say that it’s 2:30pm and you need a copy of the database placing on a test server in order to verify some pending changes. Therefore you simply take a backup of the database and restore the backup to the test server. Simple.

Now, at 4:05pm there is a corruption and the only option is to restore your database from backups. So you follow the correct procedure:

Full backup
Midday differential
1pm, 2pm, 3pm, 4pm Transactional backups

However, when you attempt to add the 3pm transactional backup it fails with an error (perhaps like the following):

Basically what has happened is that the backup you took at 2:30pm has broken the chain. The 3pm and 4pm transaction log backups are now for THAT backup and not for the midnight copy.

Therefore if you have subsequently deleted the 2:30pm backup you will have lost all data since the 2pm transaction log backup.

However, if you were to select “copy-only backup” then this problem would be avoided and your backup chain would remain intact and complete.

Ensuring people always use this option when making out of sync backups can be a very safe practice to follow.

Breaking the Chain

The other way to break a backup chain is to change the recovery model of a database. For example, when bulk loading or running something that you don’t want logged (or minimally so), some people may change the recovery model from Full to Simple (or Bulk_Logged). Once this action is complete they will revert back to Full. However this will invalidate the transaction log backups from then on.

The way to prevent this breaking of the chain is to run either a Full or Differential backup after reverting back to Full recovery.

It is a common myth to think that only a Full backup can restore / restart the backup chain, but this is not the case. A Differential backup will also suffice.

NB: Comments will only appear once they have been moderated.