THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did You Know? There's always more to learn

I was at the PASS Conference in Denver last week, as were many of my fellow bloggers. Although I didn't post a day-by-day report, I did actually learn some new things at PASS, and not just from the developers on the SQL Server team at Microsoft.

In addition to learning new things, I also had a chance to have fun. The SQL Server MVPs went bowling Thursday night along with Microsoft employees who were attending the conference. I learned that I am still a terrible bowler, but that it doesn't affect how much fun I can have. Here is picture of me, along with Lara Rubbelke and Cindy Gross from Microsoft:

http://www.facebook.com/photo.php?pid=180403&id=635018154&l=e968b

One of the new SQL Server things I learned involved the backup option COPY_ONLY, which was presented in a very entertaining session called "Disaster Recovery for the Paranoid DBA", by James Luetkehoelter (who also blogs on this site).

I knew that COPY_ONLY could be used when backing up the transaction log, so that you could make multiple copies of the same log contents. In other words, backing up the log with COPY_ONLY does not truncate the log.

However, I was not aware of the ability to use COPY_ONLY when backing up a database, and if someone had told me it was possible, I would have wondered what the point of it was. As far as I was concerned, backing up a database was always just a copy operation.

But if I had thought a bit more, I would have realized that backing up a database does more than just copy the data to the backup device. It records information in the msdb database, and more importantly, it zeros out the Differential Change Map (DCM) bitmap. For details on the DCM, you can see the topic "How Differential Backups Work" in the Books Online.

Imagine this scenario: You backup your full database every Sunday, and then every evening during the week you make a differential backup. A differential backup normally copies all extents modified since the last full backup. You are of course backing up your log at more frequent intervals. If there is a database failure, you start the restore process by first restoring Sunday's full database backup, and then restoring the most recent differential backup.

But what if one of your developers needs to make a copy of your database for testing purposes, so Tuesday afternoon they backup the database, restore it to the test server and delete the backup, which they think is no longer needed since they now have a copy of the database on the test server.

You continue to make differential backups, but now your differential backups are capturing the extents changed since Tuesday's full database backup. If there is a failure on Friday afternoon (right before you're about to leave for the weekend, of course) and you need to restore the database, you first restore Sunday's backup. However, your Thursday evening differential backup will fail when you try to restore it, because it is based on Tuesday's full backup, not Sunday's full backup.  Oops....

If your developer had backed up your database on Tuesday with the COPY_ONLY option, the DCM bitmap would not be cleared, and subsequent differential backups would still backup all the extents that had changed since Sunday. Of course, this assumes that your developer knows to use COPY_ONLY, or that you make the backup yourself, and remember the COPY_ONLY.

So now I know....

Have fun!

~Kalen

 

Published Thursday, September 27, 2007 2:39 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement