For many DBA’s, copy-only backups introduced in SQL Server 2005 are a little-known and little-used feature of SQL Server. However, they can be mightily useful whenever you need ad hoc backups and do not want to interrupt and mess up your existing backup schedule. Read on to find out more.

Copy-Only Backup

SQL Server copy-only backup is a backup that is completely independent of the sequence of standard SQL Server backups. Sometimes it is necessary to make a backup that will not affect the following processes of backup or restore for that database. For this purpose use SQL Server copy-only backup.

To understand how does copy-only backup work let us consider a backup schedule and learn how does it work?

The Backup Schedule – how does it work?

A typical backup schedule for a busy database may consist of a full daily backup plus several differential backups throughout the day. The schedule for a less-busy setup may shift the full backup to weekly and the differential backups to daily. This ‘chain’ of backups is very useful in case you need to restore your database.

Now, whenever you perform a restore operation, the SQL Server engine needs to ensure that the backups are restored in the correct order. To do this, the server stores an LSN (Log Sequence Number) in each backup file. The LSN is what ensures the correct sequence when performing a restore.

But what if you need a backup in between the week or day? For example, you have just made some changes to your production database and need to take a backup of those changes to apply immediately to your standby or testing database to replicate a certain scenario, but you don’t want to wait until the end of the day/week to take this backup. You have 2 options:

1. Take a normal full backup. But this messes up your LSN differential chain, and if you need to restore your databases, you better have the ad hoc backup you’ve just taken. If not your backup chain is broken, because if you manually take a full ad hoc 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 ad hoc job it will fail as the LSNs no longer match. A funny alternative is to look at it as a train of ducklings following their mother duck. If you introduce a new ‘mother’ halfway and split the train, all the ducklings behind the new impostor mother will now follow her wherever she goes, instead of following the original mother (and the original will be followed only by those ducklings that were directly behind her and in front of the impostor). So you are now breaking up a happy duck family.
2. Take SQL Server copy-only backup. This is a full backup that does not alter the differential chain. This way you do not affect the LSN chain at all. The LSN’s of any differential backups taken after the new ad hoc backup still refer to the original full backup, not to the impostor. Hurrah! The duck family is still united!

  How to take a copy-only backup

1. To take a copy-only backup in SQL Server Management Studio, navigate to the backup options window (right-click on Database, select Tasks -> Back Up…). Then check the Copy-only Backup checkbox. Note that SQL Server copy-only backups can only be full backups – they cannot be differential or act as the ‘base’ in a differential backup plan.

2. Using T-SQL, you can also specify the WITH COPY_ONLY option to achieve the same. The basic syntax is shown below; also read about copy-only backups on the MSDN site:

BACKUP DATABASE database_name TO <backup_device> … WITH COPY_ONLY

Using SQLBackupAndFTP

When using the SQLBackupAndFTP tool, taking copy-only backups is a breeze. You simply go to the “Backup options” section and check the Copy Only checkbox. That’s how to use SQLBackupAndFTP to ensure the duck family is still one.

Summary

Copy-only backups are one of those options that DBA’s should be using more. They are truly useful, and without them, life would be just a bit more difficult. You need to first know about LSN’s and your backup schedule to use SQL Server copy-only backup effectively, but once you do you will be a better DBA.