The Copy Only Backup… What is it and why use it?

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.

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to The Copy Only Backup… What is it and why use it?

  1. Pingback: Something for the Weekend - SQL Server Links 04/05/12

  2. oleg says:

    Thanks for your article, but Full Backup in SQL Server 2005/2008/2008R2 doesn’t brake log chain but Copy_only very usefull for ad-hoc transaction log backups.
    Excuse my poor English (it’s very hard for me write on your language, but i can read without problems 🙂 ) and, please, watch this screencast for proof of my words: http://screencast.com/t/x6J8G5smQN3

  3. Patrick Flynn says:

    Hi Mitch

    Your comments about TLog backups being tied directly to the preceding full backup are incorrect. For some discussions around this myth please look at:
    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(3030)-backup-myths.aspx (30.5) and at
    http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx

    The problem with AdHoc backups is usually related to
    (a) Differential Backups (A COPY_ONLY backup does not reset the differential_base_lsn)
    (b) Automated Recovery Scripts . These may rely on Backups being placed in specific locations.
    (c) Log Backups in a log shipping scenario. This will be an issue when the AdHoc log bacup is not placed in directory used by log shippng jobs.

    Note. If log backups were directly tied to the last full backup as you described then log shipping would be broken as soon as a Full backup was taken.

    For a more detailed description you can look at:
    http://www.mssqltips.com/sqlservertip/1075/copyonly-backups-with-sql-server-2005/

  4. Bryan Powell says:

    Been a long time since we chatted, Mitch! I hope all is well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s