@zadam,I would like to say some bit about media set. I also expect some more details from our expert like
Mr. Aaron Bertrand & some other StackExchange DBA Experts.
First of all , what is media set.
A successful backup operation adds a single backup set to the media
The backup set is described in terms of the media set to which the backup belongs.If the backup media consists of only one media family, that family contains the entire backup set.
A media set is created on the backup media during a backup operation by formatting the backup media.
Backing Up to an Existing Media Set
When you are backing up to an existing media set, you have the following two options:
- Append to the existing backup set.
Note Here:- which is the default behavior of the BACKUP, can be explicitly specified by using the NOINIT option.
- Overwrite all existing backup sets with the current backup, leaving
the current media header in place.
Note Here :- Overwriting existing backup sets is specified by using the INIT option of the BACKUP statement.
BACKUP DATABASE DatabaseName
TO DISK = 'd:\bu\mm_labeled.bak'
WITH MEDIANAME = 'BackupsToTheDDrive',
MEDIADESCRIPTION = 'Portable Drive Used for Backups',
Grant Fritchey Here The MediaSetID is a GUID that uniquely defines this set.It will also be recorded in msdb so you can link a backup to a particular server.
For example :
FROM dbo.backupmediaset AS b
WHERE b.media_uuid = '720C1417-3889-4730-9D33-74EBF11E854A' ;
For example if your full backup TSQL will be like that
Backup Database DatabaseName
Name='DatabaseName-Full Database Backup'
Every volume of backup media (disk file or tape) contains a media
header that is created when by the first backup operation that uses
the tape (or disk). That header remains intact until the media is
If you will execute this TSQL statement to know about
select * from msdb.dbo.backupmediafamily;
Here i would like to say that if you shall take backup in Disk. So always your 'family_sequence_number' will be 1. But if you shall take backup in Tape then 'family_sequence_number' will be like
the sequence number of the initial tape is 1, the sequence number of the second tape is 2, and so forth.
The media header contains all of the information required to identify the media (disk file or tape) and its place within the media family to which it belongs. This information includes:
The name of the media.
The media name is optionally, but we recommend consistently using media names that clearly identify your media. A media name is assigned by whoever formats the media.
The unique identification number of the media set.
The number of media families in the media set.
The sequence number of the media family containing this media.
The unique identification number for the media family.
The sequence number of this media in the media family. For a disk file, this value is always 1.
The Microsoft Tape Format media label or the media description (in free-form text).
The name of the backup software that wrote the label.
The unique vendor identification number of the software vendor that formatted the media.
The date and time the label was written.
Note :- In case of Disk, The sequence number of this media in the media family.This value is always 1.
For your ref of Tape Media Set along with Backup set Media Sets, Media Families, and Backup Sets (SQL Server) and