Dec 032015

I’m already at peace with the fact that I’ll never know all of SQL Server’s secrets, but that doesn’t stop me from being surprised every time I figure out something new. In this case, it’s another “secret” hiding in plain sight (Books Online).

It turns out that the DROP DATABASE statement doesn’t just have to drop one database. BOL shows that multiple databases can be specified when separated with commas. Let’s see it in action.

First, create 4 databases:


Now drop them:


Yep, all gone.

But what if there’s an error? Re-run the CREATE statements above, but now let’s drop 5 databases instead of 4. DB4 doesn’t exist (much like Terminal 4 at Chicago’s O’Hare Airport).


The above statement will throw an error that it is unable to drop DB4 because that database doesn’t exist, but the other 4 databases are dropped without incident. The same will happen if a user is connected to one of the databases: that one will remain, but all others will be dropped.

So there you have it: you can drop multiple databases with a single statement in SQL Server. (According to Books Online this does not work in Windows Azure SQL Database.) It’s amazing the things you can learn from reading documentation!


Nov 242015

Registration is now open for Chicago’s 6th SQL Saturday! It will be taking place at DeVry University’s Addison Campus on March 5 2016. Please join us!

If you’re not familiar with SQL Saturday, it’s a free day of training for data professionals, covering a wide variety of topics relating to database administration, database development, business intelligence, and professional development. It’s also a wonderful opportunity to network and meeting others in the SQL community. Registration for the event itself is free, with an optional lunch provided for $15. Space is limited – register today!

The call for speakers is also open – please submit your session(s) by January 5 2016! We are always looking for new presenters. One of the founding principles of SQL Saturday is to develop new community speakers, so if you’ve ever wanted to present at an event like this, consider this your opportunity to submit something!

We hope to see you in March!

Nov 192015

When backing up a database or transaction log, SQL Server needs to know two basic things:

  • What information the backup should contain
  • Where it’s going to be written

Many options exist to control the contents and behavior of a backup: whether it’s full, differential, or a log, if it should be compressed, copy_only, encrypted, the list goes on and on. Most people will learn these options, understand their effects, and consider themselves to have mastered SQL Server backups. While they’re not totally wrong, there’s still a lot more to the story: where the backups are going. While it’s incredibly simple to use the TO DISK option and write the backup out to one or more files, there are a lot of possible controls over how and where a backup is written out to tape or disk. This post, along with a few others, will discuss those features.

Basics of Media Sets and Backup Sets

Whatever our backup contains, it needs to go somewhere. That somewhere is called a media set. The concept of a media set may be unfamiliar because their creation is handled automatically when taking a backup. A media set consists of one or more devices of a single type. Currently, that means:

  • One or more files or
  • One or more tapes or
  • One or more Azure blobs (in SQL Server 2014 and later)

Media sets cannot contain more than one type of device, so creating a media set that utilizes both a file and a tape is not possible.

Backing up a database will create the necessary media set (if it doesn’t already exist) and then write the backup to the media set. Technically the backup being written to the media set is known as a backup set.

But enough talk, let’s try some examples and see how this all works:

First, let’s clear out the backup history so the results of these examples are more apparent. The sp_delete_backuphistory procedure takes a date parameter and will delete all records older than that date. Passing in the current date means everything will be cleared out. As with all my code examples, this should only be executed on a test machine, and never in production environments!

SET @Oldest_Date = SYSDATETIME();
EXEC msdb.dbo.sp_delete_backuphistory @Oldest_Date;

Now, let’s create a user database and back it up to a single file.

USE [tempdb];
TO DISK = 'C:\Temp\DemoDB1.bak';

Creating this backup automatically created a media set. SQL Server maintains a table of media sets in the msdb database. To view it, run the following:

SELECT * FROM msdb.dbo.backupmediaset;

(click to see all columns)

Looking at the results, there’s not a whole lot to a media set. It’s mostly identifiers and a few properties. There’s nothing in there at all about the backup itself; the media set is merely a container that holds backups. Information about the backup is stored as part of the backup set, which has its own table in msdb. The following query displays information about the backup set. Fill in the media_set_id value from the media set query above before running it.

SELECT b.* FROM msdb.dbo.backupset b
INNER JOIN msdb.dbo.backupmediaset m ON b.media_set_id = m.media_set_id
WHERE m.media_set_id = <enter media_set_id here>;

(click to see all columns)

The backup set contains lots of information about the backup and state of the database when it was taken, including the backup’s duration both in terms of time and the log sequence number. Much of this same information can be found in the backup header, which can be queried using the RESTORE HEADERONLY command:

FROM DISK = 'C:\Temp\DemoDB1.bak';

(click to see all columns)

Multiple Backup Sets Per Media Set

A media set is effectively a container, and containers that can only hold one of something are pretty boring. To make sure media sets aren’t boring, Microsoft gave them the ability to contain multiple backup sets. Now let’s take another backup of the same database, and write it out to the same file as before.

TO DISK = 'C:\Temp\DemoDB1.bak';

But what happened to the first backup we took? Nothing at all, actually – it’s still there. The media set (backup file) now has a second backup set containing the second backup.

This can be observed by either querying the backupset table or the backup header. Two entries will now be shown.

SELECT b.* FROM msdb.dbo.backupset b
INNER JOIN msdb.dbo.backupmediaset m ON b.media_set_id = m.media_set_id
WHERE m.media_set_id = <enter media_set_id here>;

(click to see all columns)

Backups of Multiple Databases Per Media Set

It’s also completely possible for backup sets of different databases to be contained in the same media set. To prove this, let’s add a backup of the master database to our existing media set.

TO DISK = 'C:\Temp\DemoDB1.bak';

The backup set query from above will now show the media set contains three backups: 2 of DemoDB and 1 of master.

(click to see all columns)

With multiple backup sets being contained within a media set, things get a little bit trickier. It’s now that much easier to lose multiple backups when only a single file needs to be deleted, lost, or damaged. Restores are also a bit more work, because if the media set contains multiple backup sets, you’ll need to specify which one is being restored. This is done by using the RESTORE statement’s FILE option (which adds to the confusion because in this case, “file” really refers to the backup set.)

To restore the 2nd backup taken of the DemoDB database into a new database called DemoDB_2, the syntax would be as follows:

FROM DISK = 'C:\Temp\DemoDB1.bak'
MOVE 'DemoDB' TO 'C:\Temp\DemoDB_2.mdf',
MOVE 'DemoDB_log' TO 'C:\Temp\DemoDB_2.ldf';

If you don’t specify which FILE to restore, it defaults to FILE = 1, the first backup set in the media set. The MOVE options tell SQL Server to restore the database files to a new location, rather than attempting to overwrite the existing DemoDB files (and failing).

Backup Compression

And then there’s compression. If you look above to the query against msdb.dbo.backupmediaset, you’ll see that the result set contains a column called is_compressed. SQL Server native backup compression occurs at the media set level. All backups in a media set must have the same level of compression, so the entire media set will contain backup sets that are all compressed, or all uncompressed. Try adding a compressed backup of DemoDB to our existing (uncompressed) media set and you’ll be greeted with the following error.

TO DISK = 'C:\Temp\DemoDB1.bak'

As this error leads us to believe, there are many backup options that can control the behavior of backup sets and media sets. My next post in this series will discuss and explain them.

Nov 042015

I had a beyond amazing week at SQL Saturday Portland and PASS Summit, but now it’s back to reality. Time to catch up on sleep, return to the daily grind of work, and enjoy the comforts of home. Gotta sort through all those photos, start counting down the 51 weeks until the next Summit (PASS has a countdown clock up on their site!) and start thinking about which sessions to submit when the call for speakers opens in a few short months.

In past years I’ve given a play-by-play of things I did, but there’s so much that goes on that week that writing it up properly would be way too long of a post. I’ll keep it simpler this time around and stick to just a few thoughts.

An annual milestone

PASS Summit is one of my milestones of the year. It automatically triggers thinking back to the previous one and then realizing that another year has gone by and wow, I’m older. As I think about it, it’s a lot like Christmas: something I look forward to all year where I get to have a wonderful time with family. Yes, it’s #sqlfamily instead of blood relatives, but there’s also the bonus of not having to take that family photo where Grandma makes everyone stand in the exact same spot every year. (I’m not kidding, we have these going back to 1990.)

Great advice: sleep when you get home

As attendees in one session were told: “If you’re going back to your hotel at 6pm, you’re doing it wrong. Hotels are for basic hygiene and *some* sleep. Other than that, get out, do things, and meet people.” I didn’t hear that advice my first year, but I’m so glad I had already met so many people on Twitter before I arrived 4 years ago. By the time I got to Seattle, I already had plenty of contacts that knew better than to let me get a good night’s sleep!

Wifi connections

In the past, wifi at the summit hasn’t been all that dependable. With 5000+ people, most of whom are carrying multiple devices, it’s not hard to understand why. This year, however, it was rock solid. I got kicked off the network exactly once, and I’m pretty sure it was my own fault. Whatever PASS did to make things so reliable this time, thank you very much!

Non-wifi connections

Connect. Share. Learn. This has been PASS’ motto for quite a while. We do it all year long in chapter meetings, SQL Saturdays, virtual chapters, and on social media. What makes the summit special to me is that it’s so much bigger than any of those things, and no computer is necessary. You can meet people in person. Shake their hand. Give them a hug. Give them a really big hug if you haven’t seen them in a while. There’s amazing learning to be had in the sessions, but for me the real learning has always taken place outside of presentations. Getting to know people. Finding out what they enjoy doing when they’re not working. Making new friendships and strengthening existing ones. It always amazes me how much we all have in common, regardless of what we do or where we come from. I took my own advice more this year than ever before. Yes, that means I spent less time attending sessions and more time connecting with people. I couldn’t be happier with the outcome.

Try something new

As many ways as PASS Summit is the same every year, I try to make it different each time as well. Last year was my first time taking the #sqltrain from Portland to Seattle, an add-on that was so much fun I didn’t have to think twice about doing it again. This time around I did a few new things, including attending different types of sessions. Hardcore SQL Server internals porn is great, and while I still got my fix in that department, I also attended some professional development and business intelligence sessions. I got to see how handsome and witty Mark Vaillancourt is when presenting, and as an added bonus his movie-themed session used my favorite clip from The Wizard of Oz. (Click that link – it’s really neat!)

I also got outside of my comfort zone this year and was more of an active participant in SQL Karaoke instead of just being a member of the audience. This took some careful prodding, but I’m glad I was persuaded. (I’ll be even more glad if no evidence of this ever surfaces…)

No regrets

Of course there’s no regrets. In all honesty, the only regret I’ve ever had in terms of PASS Summit is not attending sooner. I wish this could be my 6th or 7th year instead of my 4th. But I know there’s plenty of people who haven’t been to one, let alone four. I’m extremely fortunate in that sense.

The perfect photo

I’m always searching for the single perfect photo to sum up an event or era of my life. My 2013 PASS Summit Wrap-Up used what I consider to be the perfect picture representing my high school years: a huge group of my friends getting together in my parents’ basement. I’ve always loved getting people together to have a good time. PASS Summit is way more people, and a lot of times you’ll find us in a much larger basement, but the essence is the same.

More often than not, the perfect photo isn’t a group shot. Group photos contain lots of stories, but don’t really tell a story. My favorite photo from all 5 years of college marching band is a seemingly random picture taken by my father 11 years ago this weekend. It’s of my best friend from college and I playing in the stands. It’s a cold November day, but you’d never know that by looking at the bandos. The sun perfectly captures the reddish Illini orange of our capes, as well as a few of the flaws of our uniform. My gloves are on my shoulder because pockets were difficult to get to. Her cape is reinforced with a safety pin because they were too heavy for buttons alone to support. Campus buildings can be seen off in the distance, and the football game is nowhere to be found. (The Fighting Illini weren’t all that great that year…or any year after my first, for that matter.)

A perfect photo for this or any year’s PASS Summit? I’m not sure I have one. There’s so many things going on with so many wonderful people. Also, perfect photos tend to become more apparent after-the-fact. This is a chapter of my life that’s still ongoing, with plenty more people to meet, experiences to share, and memories to make. In that sense, I hope this perfect photo hasn’t been taken yet.

Oct 222015

It’s nearly upon us! Next week, data professionals from around the world will gather in Seattle for the spectacle that is PASS Summit. A conference (and family reunion) like no other, we’ll share knowledge, war stories, fellowship, and also germs (because I’m pretty sure I’ve come home with some strain of the nerd flu each year.)

With over 200 technical sessions, there’s something for pretty much everybody. Building a personal schedule always proves to be an exercise not in finding a session you want to attend, but rather choosing which session you absolutely cannot miss because there’s 3 others you also want to see in that same time slot.

Yet amidst this bounty of knowledge being given away by leaders and experts in their fields, there are times when I opt to simply not attend a session at all. Instead of listening to a presentation, you’ll probably find me in a beanbag chair in the community zone chatting with others, out for coffee, or exploring some part of Seattle I’ve yet to find. Why? Because PASS Summit is about way more than attending sessions. It’s about community. It’s making connections and new friends, and catching up with the old ones you haven’t seen in a while. The real power of the summit is being in the same place as all these people, interacting, and getting to know them. Time is extremely valuable that week, and while sitting in presentations is a solid way to invest it, there are other ways to spend it that are just as valuable, if not moreso.

Buy the videos

Really. They’re so worth it. Like I said earlier, it’s not going to be possible to attend all the sessions you’ll want to see. And to be honest, you won’t always be in the best physical or mental shape to learn in a session. Perhaps you were at an evening activity pretty late and now you’re exhausted, or maybe you went on #sqlrun and decided that a #sqlshower* might be a good (and courteous!) alternative to making the first session of the morning. You can watch the videos whenever you like, and as an added bonus, they also support pause and rewind functionality.

Microsoft also gets it

Earlier this month it was announced that Microsoft would not be offering 50% off certification exams on site, as they have done in the past. At first I was upset, but then I continued reading and saw that they would be offering 50% off exam vouchers to conference attendees instead. This is a much better deal in my mind – you are now free to take a discounted exam back at home where you won’t have to miss out on sessions or other activities to do so. It’s one fewer distraction from making connections and learning from others, whether that’s inside or outside of a session.

To sum things up, your time at conferences is very valuable. Everything you do is paid for with time, over and above all the monetary expense you and/or your employer incurred to be there. However you spend your time, make sure you’re getting the most out of it. If you’re going to be attending PASS Summit this year, I’ll see you there, hopefully outside of a session!


* For the record, #sqlshower is NOT a group activity.