Understanding SQL Server Media Set, Media Family and Backup Set

I am sure that you have been taking backups of SQL Server databases and you know that backups can be taken either to a disk or a Windows Azure Blob Storage service. However, many of us are unaware about some terms used with backup operations, such as Media Set, Media Family and Backup Set. Let's try to understand these with an example.

Media Set

This is the main container that holds backup sets using backup devices. Generally we take a backup of the database without mentioning the name of Media Set, hence the device we used is attached to a media set that has no name. Remember, Media Name is optional There is no harm of not mentioning the name for Media Set but it is always advisable to name it because it helps us to identify the correct backup sets.

Media Family

This refers the device (single non-mirrored device or set of mirrored devices) used for holding the backup in the Media Set. If Media Set has one device, then Media Set contains one Media Family. If Media Set has two devices (say that you take a backup to two disk files), then it contains two Media Families. Each Media Family in the Media Set is assigned with a number called Media Sequence Number.

Backup Set

Backup Set is the content of the database backup we take to the Media Set. If you take a backup of your Sales Database, then it is the Backup Set in the Media Set. If Media Set contains one Media Family (means one device), content is written to it. If it contains two Media Families (or more), content is distributed among families (devices), working similar to stripe set.

Media Header

Each and every Media Family in the Media Set has a header that holds information such as Name, Unique identification, Sequence number, etc. This header remains until the Media is reformatted.

Let's take a backup of a database and see how this works. I have a database called Marketing and I am taking a backup of my database to two files. Note that I have mentioned the Media Name which is the Media Set Name. As mentioned above, it is not a must but it is always better to name it.

Here the TSQL code for backing up;

USE master;
GO

BACKUP DATABASE Marketing 
TO DISK = N'D:\Backup\Marketing\BackupDevice01.bak'
 ,  DISK = N'D:\Backup\Marketing\BackupDevice02.bak' 
WITH NOFORMAT, NOINIT,  MEDIANAME = N'MarketingMediaSet01'
GO

Here is the way of doing the same with GUI.

Just like the way described above, you can click on the OK button and take the backup of one of your databases. Since I have used two files, my backup is distributed (approximately 50% per each) among two files. Let's query some tables in the msdb database to see how Media Set, Media Families and Backup Sets have been created.

USE msdb;
GO

SELECT * FROM dbo.backupmediaset WHERE name = 'MarketingMediaSet01';
SELECT * FROM dbo.backupmediafamily WHERE media_set_id = 35;
SELECT * FROM dbo.backupset WHERE media_set_id = 35;

If I did not mentioned the name for Media Set, I will see blank for name of the Media Set. As you see, Media Set named MarketingMediaSet01 is created and two Media Families have been created for each device I used. I took only one backup, hence there is only one Backup Set for this media set.

This is what I used for explaining the same in my workshops;

If I take another backup of Marketing Database again to the same without overwriting (WITH NOINIT), I will see an additional Backup Set under same Media Set.

I can illustrate the Media Set now as below;

Let's take one more backup to the same Media Set. This time, I am going to take a backup of different database, which is Adventure Works.

USE master;
GO

BACKUP DATABASE AdventureWorks2014 
TO DISK = N'D:\Backup\Marketing\BackupDevice01.bak'
 ,  DISK = N'D:\Backup\Marketing\BackupDevice02.bak' 
WITH NOFORMAT, NOINIT,  MEDIANAME = N'MarketingMediaSet01'
GO

I can take it without any issue and I can see another Backup Set added to my Media Set.

Here is the illustration for it now;

You may ask that it is possible to take a backup of another database (or same) without mentioning the Media Name. Yes, it is possible, you can take a backup to same devices without adding the Media Set name but it will be added to the same Media Set.

However, if you try to use the same devices with a new Media Name, you will get the following error because it is not possible to attach these devices to another Media Set.

Msg 4037, Level 16, State 1, Line 1
The user-specified MEDIANAME "NewMediaSet" does not match the MEDIANAME 
"MarketingMediaSet01" of the device "D:\Backup\Marketing\BackupDevice01.bak".
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.