What is Copy Only Backup?

The copy-only backup is actually a very important tool in all production DBA’s arsenal. A copy only backup is essentially a ‘snapshot’ of the database at that point in time that is completely independent from any sequencing of backups used for a restore.

Why is it important some might ask, well, let me explain. Almost all production SQL environments have some sort of back up strategy which includes some combination of full backups, differential backups and/or Transactional log backups. For instance, a full backup is taken on a database every weekend. This creates a starting reference point for a restore.  After the full backup, differential backups are taken nightly with additional transaction log (TLog) backups taken at specified intervals throughout the day to create what is called a log chain. All of these differential and TLog backups are tied directly to the preceding full backup. A Log Sequence Number (LSN) is created to keep all the backups in order. When a restore is required, the full backup must be restored with all the proper differential and TLog backups. If one is missing, the restore will fail. The important thing here is that after a full backup, all of the differential and TLog backups following it are tied directly to that Full backup.

So, assuming we are all very organized and keep all of our backups automated and documented properly, we should be able to perform a point in time restore without issue. But what happens if one of your co-workers takes an AdHoc full backup of a database to restore to a development environment without telling anyone. Later that day you need to restore the database to a point in time after the AdHoc back up was taken by your co-worker? The restore will fail since the Log Chain was broke by the AdHoc full backup and the LSN’s are no longer in sync with the original full backup. Basically, any TLog backup taken after the AdHoc backup will now be associated to that backup.  In this case, had the person simply taken the AdHoc backup using the Copy-Only option, there wouldn’t have been any issues with the restore since the chain of backups wouldn’t have been broken.

So how do we take a copy-only backup of a database? It is as simple as just adding a ‘with’  statement to the backup command as seen below.

– Create full backup with Copy Only option
BACKUP DATABASE MyDataBase
TO DISK = ‘D:MyDatabase_CopyOnly.bak’
WITH COPY_ONLY
GO

The copy-only feature has been available since SQL 2005, but had to be written in the T-SQL backup statement. In SQL2008, the Copy-Only feature is available in the GUI for ease of use.

Keep in mind that the copy only feature is not available for Differential backups and the option in the GUI will actually be ‘greyed’ out.  The copy-only feature should always
be considered for any AdHoc backup that is not part of the normal backup schedule in your environment.