Backup media

In previous chapters, we worked with backups stored to disk files. It's a very common destination of backup because tape drive as a backup destination must be attached directly to the server. Due to the usual usage of backup tape devices to back up overall company infrastructures, SQL Server does not improve tape backup possibilities and relies on third-party backups. That's why all the following examples will use disk files as the backup device.

If SQL Server databases are going to be backed up by third-party backup devices, never mix the execution of these backups with SQL Server native backup!

When we need to improve the speed of the backup, we can join more backup places into one set called media set. The media set is formed of one or more devices of the same type. SQL Server spreads data across all devices in the media set evenly. We can imagine the media set as a striped disk. The media set is created the first time the backup is executed. The following example creates a media set:

BACKUP DATABASE <database name> TO
DISK = '<path to first file>',
DISK = '<path to second file>'
WITH
MEDIANAME = '<name of the media set>'

Every backup saved to the same media set is then called backup set. Once the media set is created, it must be used as a whole. An attempt to use one of the files for additional backup without using the whole media set will fail. Using media sets makes backup operations faster, but it also increases the risk of backup loss.

Let's see the following example:

BACKUP DATABASE <database name> TO
DISK = '<path to first file>'
-- second file from previous example is not used
WITH
FORMAT

The FORMAT option causes the media set to break and all backups saved there are lost! So use media sets carefully.