Kalen Delaney : PASS

Did You Know? My PASS Demos and Social Networking

Kalen Delaney — Mon, 15 Nov 2010 22:30:00 GMT

My PASS demos are now available on my website for my Precon Session on SQL Server Concurrency: Locking and Blocking and Row Versions, Oh My! and for my Spotlight Session on Seeking SQL Server Secrets. You can download both sets of scripts from www.SQLServerInternals.com/conferences...(read more)

Did You Know: SQL Geeks for Good

Kalen Delaney — Thu, 04 Nov 2010 19:04:00 GMT

Are you going to PASS next week? We're not going to have anything to compete with the big release and book-signing for the SQL Server MVP Deep Dives book like we had last year, but there is something special you can do this year. A former student is collecting...(read more)

Geek City: 24Hours of PASS Followup

Kalen Delaney — Thu, 16 Sep 2010 18:20:00 GMT

Thank you everyone who attended my 24HOP session yesterday. It was a wonderful event to be part of. Here are a few of the questions that I was asked, that didn't get completely answered. Question: Can we set READ_COMMITTED_SNAPSHOT ON for just a table...(read more)

Did You Know: 24 Hours of PASS is happening again, September 15-16

Kalen Delaney — Wed, 11 Aug 2010 18:36:00 GMT

This time, it's being split into two parts, perhaps so that there is more of a chance that some people could attend the entire event! Noon - midnight (GMT) for two days in a row. Here are more details: http://www.sqlpass.org/24hours/fall2010/ I'll be...(read more)

Did You Know? 53 SQL Server MVPs Give Away Their Royalties!

Kalen Delaney — Wed, 30 Sep 2009 16:47:00 GMT

You've probably already read about the SQL Server MVP Deep Dives book, which will be making its debut at PASS in November. Many of my friends and colleagues have blogged about it already. But just in case… SQL Server MVP Deep Dives is an all-volunteer...(read more)

Did You Know? 24Hours of PASS recorded sessions

Kalen Delaney — Thu, 10 Sep 2009 00:54:00 GMT

Some of the sessions from 24 Hours of PASS have been made available for viewing here . The currently available sessions are those by speakers who will be presenting a pre-conference seminar at the the big PASS conference in November. In addition to me,...(read more)

Geek City: 24 hours of SQL Server!

Kalen Delaney — Fri, 14 Aug 2009 01:13:00 GMT

I know, my Geek City posts are usually really technical, but I figured it’s only Geeks who are going to want a solid 24 hours of SQL Server presentations… so this post is dedicated to all the SQL Server Geeks out there! I will be presenting as part of...(read more)

Did You Know? A P.S. to my PASS presentation

Kalen Delaney — Thu, 27 Sep 2007 20:51:00 GMT

This is hot off the presses!

I will be presenting for the UK Server Server UG in Reading, November 16 at the Microsoft campus. The presentation will be similar to my preconference session at PASS, with a few enhancements and extra details.

You can read about it here:

http://sqlserverfaq.com?eid=103

If you're UK-based, I hope to see you there!

~Kalen

Did You Know? There's always more to learn

Kalen Delaney — Thu, 27 Sep 2007 20:39:00 GMT

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