SQL Server BACKUP WITH FORMAT

When doing a SQL Server database backup using the T-SQL BACKUP statement, does WITH FORMAT only wipe out the specified Media Set or does it wipe out all Media Sets 'associated' with the database?

BACKUP DATABASE ExampleDatabase To DISK = N'...'
WITH FORMAT, 
MEDIANAME = N'ExampleDatabase-Media-yyy-mm-dd-hh-mm', 
NAME = 'ExampleDatabase-yyy-mm-dd-hh-mm',
COPY_ONLY
...

• will this result in only the Media Set with name ='ExampleDatabase-Media-yyy-mm-dd-hh-mm' being wiped (if it exists)?

The context is that I am doing some ad-hoc backup and restores from one server to another. And I want to make sure that the FORMAT in my command will not format any other Media Sets that might have been created.

I want to make sure that my backup statement that uses FORMAT will not interfere with any other Backup maintenance plans that are in place by destroying Media Sets other than the one I define in my statement.

@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
set.

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.

For Example:-

BACKUP DATABASE DatabaseName
TO DISK = 'd:\bu\mm_labeled.bak'
WITH MEDIANAME = 'BackupsToTheDDrive',
MEDIADESCRIPTION = 'Portable Drive Used for Backups',
INIT;

As per 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 :

SELECT  *
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

Use DatabaseName
Go
Backup Database DatabaseName
To Disk=N'E:\Database_Backup\DatabaseName_Backup_16022016\DatabaseName.bak'
with FORMAT,
Medianame='E_SQLServerBackup',
Name='DatabaseName-Full Database Backup'
GO

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
reformatted.

If you will execute this TSQL statement to know about media_set_id & 'family_sequence_number'.

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 see Media Sets, Media Families, and Backup Sets (SQL Server) and
Here

StackExchange Database Administrators Q#132466, answer score: 3