SQL: Mirroring SQL Server backups is a fault-intolerant option

There are a number of words related to SQL Server that are overloaded. One of those is “mirroring”.

Mirroring backups has nothing to do with other concepts like database mirroring.

Mirroring backups allow SQL Server to send the same backup to more than one location at the same time. The Books Online page for the BACKUP command describes the clause this way:

“MIRROR TO <backup_device> [ ,…n ] Specifies a set of up to three secondary backup devices, each of which mirrors the backups devices specified in the TO clause. The MIRROR TO clause must specify the same type and number of the backup devices as the TO clause. The maximum number of MIRROR TO clauses is three.”

Up to three secondary backup devices can be used concurrently. I’ve seen people who assume that this means that if one of their backups fails (perhaps because they’ve run out of disk space), that at least they will have one or more other backups.

What that page doesn’t spell out is that if any one of the backups fail, then all the backups fail.

This makes mirrored backups a fault-intolerant option. 99% of the time, I think you should avoid it. Create a single backup then copy it if that’s what’s needed.

2018-03-26