Sql-server – have to change Media Sets to create a Full Backup to a new file and what does that mean

backupsql serversql-server-2016

I'm trying to create an ad-hoc Full Backup as a snapshot of the database in time and when I added a new file to backup to I get the following error:

Error

I see I can surpass this error by going to Media Options and changing the Overwrite Media to "Back up to a new media set, and erase all existing backup sets". What does this mean?…will I lose my existing backups if I do that?

Best Answer

It's quite a long topic and for the most part it relates to technology that is irrelevant in this day and age (more specifically: tape storage). However here is a quick summary:

media set

An ordered collection of backup media, tapes or disk files, to which one or more backup operations have written using a fixed type and number of backup devices.

media family

Backups created on a single nonmirrored device or a set of mirrored devices in a media set

backup set

The backup content that is added to a media set by a successful backup operation.

In short, if you create a new media set on a device, then yes, you will lose all of your previous backups:

Creating a new media set

To create a new media set, you must format the backup media (one or more tapes or disk files). The formatting process changes the backup media as follows:

  1. Deletes the old header (if any), effectively deleting the previous contents of the backup media.

  2. Formatting a tape device deletes all previous contents of the currently mounted tape. Formatting a disk affects only the file that you specify for the backup operation

  3. Writes a new media header on the backup media (tape or disk file) on each of the backup devices.

For more information, checkout the Media sets, media families & backup sets documentation over at Microsoft Docs.