page contents

About the Post

Author Information

Microsoft SQL Server Copy_Only Backups

One of the great books, I have read this year and have really enjoyed has been Rod Colledge’s (Blog | Twitter) SQL Server 2008 Administration in Action. I have really enjoyed this book and for being technical it was really well laid out, plus easy to follow. My copy has been thoroughly worn and marked up with notes. If you are  wanting to be a SQL Server DBA or you want to improve your skills, I highly recommend this book.

Today I am going to cover one of his Chapter 10 topics about backups, specifically Copy_Only.

First off we need to briefly cover that there are three different recovery models for SQL Server: Simple, Full and bulk logged.

As you maybe aware, each database has a transaction log and it is unique for each database. The head of a log chain is a full database backup and each transaction log backup forms a part of this chain. To restore a database to a point of time, you need to have a database in Full Recovery mode (or Bulk logged) the restore is based upon a requirement to have an unbroken chain of transaction log backups. In this recovery model, you will need the correct log sequence numbers and have them “played back” in order. If you tried to do them out of the LSN order, you will get an error like the message below:

Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 24100000002283000001, which is too recent to apply to the database.
An earlier log backup that includes LSN 24100000002236000001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

So what do you do in the situation where you have  current database backup rotation but you want to back up a database to restore on another server? For example, let’s say that a developer is designing a solution and wants to take the current database and restore it to his workstation. He does this without telling anyone, but when the next SQL Server restore happens from a differential it will fail due to this backup change. Sadly, that is when you find out what happened. Why did this happen? Well, a SQL Server differential backup uses a Differential Changed Map (DCM) to track which extents have changed since the last full backup.

You might be familiar with tape backup types: Differential backs up any data that has changed since the last full backup and leaves the archive bit alone.  Incremental backs up any data that has changed since the last full or incremental backup and resets the archive bit. SQL Server for the most part, the backups act like a Incremental backup where it resets the archive bit. With the Copy_Only backup, it kind of acts like a Differential backup.

In my example above with the developer when they did that backup, it reset the DCM to relate to the developer’s backup instead of what we have configured as either a maintenance plan or a SQL Agent job.What makes it ugly, is if we ask the developer for the backup he took and sadly find out that he deleted it to save space on his workstation. So that means we will have a broken transaction log with missing LSN’s which will not allow us to do a point in time restore during this time period. (We also pray that no inserts bad code or that we need to restore to this point in time)

To use this feature and allow others to take a copy of the bases for testing, all you need to do is type this command: (Click on the image below to see it in full size)

Here is the command as well in text:

–Perform a COPY ONLY Database backup
BACKUP DATABASE [AdventureWorks] TO DISK = N’Z:\Backups\SQLServer\DB\AdventureWorks\AdventureWorks_20120601.BAK’
WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N’AdventureWorks-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM
GO

Tags: , , , ,

2 Responses to “Microsoft SQL Server Copy_Only Backups”

  1. Thanks for this post. Some how this feature has been overlooked by me in the past. I will begin using the COPY_ONLY option as a general practice now, when I provide backups for developers. I guess I was spooled by the fact that I could take the backup and locate it in the same directories as the scheduled backups so I wouldn’t lose the chain. This is why I love reading fellow DBA’s blogs, I’m bound to learn something new everyday.

    Thanks again!

    September 3, 2012 at 5:01 PM

Trackbacks/Pingbacks

  1. SQL Server Recovery Model Based Upon System Database Model! | Scott Mattie's Blog - June 6, 2012

    […] […]

Copy Protected by Chetan's WP-Copyprotect.