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.

May 082015

It’s 10PM*. Do you know who your sysadmin role members are?

SQL Server’s fixed server roles control incredibly broad permissions across your entire instance. For this reason, membership in these roles should be granted carefully and reviewed often. Fortunately, reviewing role membership is extremely easy, so you can always answer the above question better than Homer.

The Easy Way: SSMS GUI

In the SSMS Object Explorer, open the “Security” folder for an instance, and then open “Server Roles”.

You’ll see it displays the 9 fixed server roles. Double-clicking on any role brings up a dialog that shows all its members:

If you hate typing, then this method is for you. Otherwise, you’ll probably like

The Easier, Faster Way: T-SQL

In SQL Server, where there’s a will, there’s a way to do something in T-SQL. And it will probably be better/faster/cheaper too. Here’s the query I use to check server role memberships:

SELECT AS Server_Role, AS Role_Member,
   mp.type_desc AS Member_Type,
   mp.is_disabled AS Login_Is_Disabled
FROM sys.server_role_members srm
INNER JOIN sys.server_principals rp ON srm.role_principal_id = rp.principal_id
INNER JOIN sys.server_principals mp ON srm.member_principal_id = mp.principal_id;

On my test server here at home, it returns the following results:

You can see the 4 columns it gives us: the name of the server-level role, the name of the login, what type of login it is, and whether or not the login is disabled. The SSMS role member dialog shown above gives no indication if a login is disabled or not, so I find that column especially handy in the query.

It’s important to note that this query returns members of all server-level roles at once. This example shows only the members of the sysadmin role because it’s the only one with members on this instance.

What to look for

  • First and foremost, do you recognize all the members of all roles, and should they have that level of permissions?
  • You’ll most likely see windows groups included in this list as well. Do all members of the group belong there?
  • You cannot remove the sa login from the sysadmin role, so I recommend either disabling it or changing its name.

Securityadmin: the most dangerous role

Ask anyone familiar with SQL Server what the most dangerous role is, and they’ll probably say “sysadmin.” While sysadmin has the keys to the kingdom in the sense that its members can do absolutely anything on the server, there is something securityadmin has that sysadmin doesn’t: secrecy.

Members of the securityadmin role can manage any login on the server, including membership in server level roles. This means someone with securityadmin rights can:

  • add themselves to the sysadmin role
  • do whatever dastardly deeds they want
  • and then remove themselves from from sysadmin

If you’re only looking at members of the sysadmin role, you will be none the wiser. When checking server-level roles, remember that securityadmin is every bit as powerful and dangerous as sysadmin. It just might save your bacon!

*It’s always 10PM somewhere

Mar 112014

T-SQL Tuesday LogoThis post is part of T-SQL Tuesday #52, which is being hosted this month by Michael J. Swart (@MJSwart). Michael is asking us to argue against a popular opinion, and I’m more than happy to do so, as this is a belief that I’ve kept to myself for quite a while.

SQL Server’s row compression feature can be an amazing tool. Not only is it lightweight on CPU usage, especially when compared to page compression, but it can save a significant amount of disk space as well. Your data also remains compressed while in the buffer pool, meaning more rows can be stored in memory, reducing the need to make slower requests to disk. On top of all that, some queries (especially those involving index scans) can see dramatic performance improvements.

In fact, row compression is so good that Microsoft’s whitepaper actually states “If row compression results in space savings and the system can accommodate a 10 percent increase in CPU usage, all data should be row-compressed.”

Yes, row compression is a wonderful thing, and the databases I maintain frequently benefit from its use.

But I hate it.

Why? Because all too often, features designed to help make things easier also make people lazy.

By far, the biggest issue that row compression addresses is poor data typing, the use of a data type that isn’t appropriate for the values at hand. For example, if a column is only going to store the values 1 through 5, an integer data type is not necessary. A tinyint data type would be just as effective, and would consume only one quarter of the space. However if you are unable to change the data type, perhaps because the database is part of an application written by a third party, row compression can be a big help.

Row compression allows the storage engine to treat fixed-width data types as if they were variable-width. This means the disk space that isn’t needed to store a value in a fixed-width column and would typically go unused can be put to work. The savings can be tremendous, and SQL Server’s data compression features are completely transparent to end users or applications – all the magic happens behind the scenes, which is why a third party application would be none the wiser.

But what if you are able to change those data types, and just don’t feel the need to do so anymore? Data compression gives you most of the advantages of proper data typing, but all you have to do to get them is flip a switch – no pesky forethought necessary. And that’s why it’s terrible. Because for every person out there who designs their databases and data types with care, there are many more who aren’t interested, don’t care, or don’t even realize it’s an option. Features like row compression that mask these issues aren’t going to interest anyone in solving them the right way.

So while row compression is a wonderful tool and can do amazing things when used properly, don’t forget it’s also an enabler.

Jan 312014

The ability to keep track of what’s being executed on a SQL Server instance, or to create a read-only copy of a database at a specific point in time can be very valuable if required by the solution you are developing. But a lesser-known fact about SQL Auditing and Database Snapshots is that not only do they work well on their own, but they also play nicely together.

The Setup

Let’s say you work for that awesome company AdventureWorks, which embraces open data so much that you can download their corporate database on CodePlex here. That database contains some super-sensitive payroll data, and they’d like to keep track of everyone who has accessed it. To do this, you set up SQL Auditing to capture all SELECT operations on the HumanResources.EmployeePayHistory table and log them to a file.

-- create a server audit
USE [master]

FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA')

-- * in a production system you'd probably want a more stringent ON_FAILURE
--   value than "continue", but for demonstration purposes it's fine


-- create a database audit specification
USE [AdventureWorks2012]

ADD (SELECT ON OBJECT::[HumanResources].[EmployeePayHistory] BY [public])


So now anytime anyone selects from that table, there will be a record of it:

-- select from the table being audited
SELECT TOP (100) * FROM HumanResources.EmployeePayHistory;

-- query the audit file
FROM sys.fn_get_audit_file('C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\*',NULL,NULL);

So far, so good. Now let’s continue with our story and say that management wants to be able to run reports throughout the workday that show the state of the database at 6:00 am. You find the appropriate solution to be a database snapshot, so you setup a job to create a snapshot daily at 6:00 am, and another one to drop it late in the evening. All the reports can then query the snapshot and get the exact state of the database as it was at 6:00 that morning.

-- create snapshot
CREATE DATABASE AdventureWorks2012_Snap ON
(NAME = AdventureWorks2012_Data,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\')
AS SNAPSHOT OF AdventureWorks2012;

But what about that audit? Since a snapshot contains absolutely everything in the database at the moment it was created, it will have all that sensitive pay history data as well. How can you audit that?

The Free Lunch

The answer is that you don’t have to worry. A snapshot contains everything in the database at the moment it was created, and that doesn’t just apply to data – that goes for settings too, including audits. If someone was to query an audited table in the snapshot, this will be recorded by the audit just like on the base database. You’ll be able to tell the query was run on the snapshot by looking at the database name column:

-- query snapshot
USE [AdventureWorks2012_Snap]

SELECT TOP (100) * FROM HumanResources.EmployeePayHistory;

-- query audit file
FROM sys.fn_get_audit_file('C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\*',NULL,NULL);

But what if you don’t want the snapshot to be audited?

The Free Lunch is at Grandma’s House

I can only speak from personal experience, but whenever I go visit my grandmother, I go knowing that I will be eating whether I’m hungry or not. She loves cooking and watching her grandchildren eat, and – let’s face it – there’s just no way to say no to grandma.

Similarly, there’s no way to say no to a snapshot. However the database was configured the moment the snapshot was created will be reflected in the snapshot as well. It’s important to remember that database snapshots are read-only, so even if you try to disable the audit on a snapshot, it won’t work:

USE [AdventureWorks2012_Snap]


The only way to create a snapshot without the audit in this case would be to stop the audit, create the snapshot, and then restart the audit. This raises other issues, namely that you probably shouldn’t be stopping an audit of sensitive data like that.

Nov 042013

I’m very proud to have contributed a chapter to Tribal SQL, which is now available for purchase at Amazon and finer bookstores everywhere. This is the first book I’ve had any part in writing, something I never thought I would do. I always hated English and literature classes in school – if it wasn’t math, hard science, or architectural history, I wanted no part of it. I credit the SQL community and blogging for slowly getting me excited about the written word.

Getting this book from idea to print was not a sprint but a marathon. While I’m sure there was plenty going on behind the scenes that I wasn’t aware of, here’s how everything came together from my point of view.

Tribal SQL CoverHow I got Involved

The first I heard about “SQL Tribal Knowledge” was from Matt Velic’s (@mvelicblog post almost two years ago. Despite being fans of MidnightDBAs (@MidnightDBAJen and Sean McCown, I missed their blog post announcing the project. Basically they wanted to get a whole bunch of unpublished people together, have everyone write a chapter, and then assemble them into a book. Seemed like a great idea to me, so I submitted a few topic ideas, and ended up writing about data compression – a feature I’ve done lots of work with and know rather well.


Actually writing the chapter was both the hardest and easiest parts for me. Getting started was difficult because I wasn’t sure about what style of writing to use. I ended up reading through about a dozen other books not for content, but to see what tone the authors took when putting their words to paper. I ended up going with something not much different from a blog post – perhaps slightly more formal. With a style established, everything flowed easily. After about 2 months, my initial draft was complete and to my liking.

Peer Editing

After the first drafts came peer editing. All the authors picked two other chapters to read and comment on. It was a great gut-check on things like grammar, effectiveness of explanations, etc. With both reviews in-hand it was back to the drawingkeyboard for revisions.

Volunteer Editing

After peer editing there was a second round of reviews, this time from outside the author pool. Over 20 bloggers and speakers from the SQL Server community volunteered their time to read and comment on our work. Afterwards, this was followed by another round of revisions.


Throughout the process, Jen and Sean were trying to find a publisher. There was some brief talk about self-publishing, but then Red Gate got involved and agreed to publish the book, now named “Tribal SQL”. This involved – you guessed it – another round of editing, this time by Red Gate’s Tony Davis (@TonyTheEditor). Tony was delightful to work with and I have tremendous respect for what he does. After a few rounds of back-and-forth with him, my chapter was done.

From this point on I’m sure there were a lot of things taking place that I didn’t know about, but the end result is that the book was published, and Red Gate was kind enough to hold a launch event at the PASS Summit and distribute copies. They also held a book signing for all the authors that were in attendance.

Would I do it again?

I would. Writing a chapter is a lot of work – writing an entire book is obviously much more, but I found it enjoyable and rewarding. Given the right topic and/or group of co-authors, I’m sure I could have just as much fun working on another one.

Write Book, Get Published, ?????, Profit?

HA! I should mention that all royalties from the sale of this book are being donated to Computers4Africa. Even if that weren’t the case, any money we might get would work out to a paltry rate when divided by the number of hours spent working on this project. If you ever decide to write a book, make sure you’re doing it for the right reasons. Money is not one of them.

Thank You

Finally, some thanks are in order. Thanks to Jen and Sean McCown for putting this all together, and to Matt Velic for blogging and making me aware! Thanks to all the other co-authors and editors for their time and hard work. Finally, thanks to the wonderful people at Red Gate, not only for publishing this book, but for the tremendous support they give to the SQL Server community as a whole.