I have been reviewing some of the features in SQL Server 2005 and thought I had a good understanding of Mirrored Media for Backup/Recovery in SQL Server 2005. After some more research, this is what I learned.
When a Backup is performed in SQL Server 2005, the same data may be written to more than one device. Basically, you can have identical backups. As we know too well, tapes DO go bad. By performing a backup operation with the exact same data written to more than one device, your odds of a bad tape ruining your recovery are diminished. This is great news. If a tape is bad from one set, the same tape from another identical “Mirrored” set may be used in its place! Yea! Remember, the devices have to be the same.
However, this is the “not so great” take on this. If a media error occurs on the backup media during the backup process, the backup fails. It is a “All or None” proposition. If it can't write to one of the “Mirrored Media” devices, the backup will stop there. The reasoning (I suppose) is that you told it to write the same data in more than one place. If it can't do the specified operation, it will fail the job.
Scenario where it helps: Backup successfully to Mirrored Media (2 identical tape drives). A bad thing happens and the database needs to be recovered. While restoring the database from Tape Drive A's tape set, the final tape (it always seems to be that final tape) reports a media error. You get the same tape (the last tape) from Tape Drive's B set and the recovery continues and you are up and running.
Scenario where it does not help: Backing up to 2 identical tape devices. Tape Drive B goes bad. The job stops - even though Tape Drive A is still a good tape drive. The database is not backed up.
As long as you understand the benefits and potential risks of using Mirrored Media for backup, take advantage of it. There are of course possibilities that you could engineer to get around the scecond scenario, but you have to prepare for it.