How can I script out a copy of the backup to multiple locations?

Is there a way to script a copy of the backup file to multiple destinations? For example, the following will split the backup into 3 segments which make up the entire backup file. But, I'm looking to send 3 complete backups to 3 different locations. Is this possible?

BACKUP DATABASE TestDB
TO DISK = 'C:\TestDB1.BAK',
DISK = 'D:\TestDB2.BAK',
DISK = 'E:\TestDB3.BAK'
GO

I'm looking for something more like...

BACKUP DATABASE TestDB
TO DISK = 'C:\TestDB1.BAK',
TO DISK = 'D:\TestDB2.BAK',
TO DISK = 'E:\TestDB3.BAK'
GO

Thanks in advance.

You want to use the MIRROR TO option. This will allow you to create up to 3 different backups - each effectively a mirror or copy of the main backup you are doing.

In your case:

BACKUP DATABASE TestDB
TO DISK = 'C:\TestDB1.BAK'
MIRROR TO DISK = 'D:\TestDB2.BAK'
MIRROR TO DISK = 'E:\TestDB3.BAK' WITH FORMAT
GO

You can see more about the commands and options for backup here. There are many other options you should look into - such as verify, checksum, etc.

A couple notes also - 1.) The WITH FORMAT is necessary the first time I execute this here since this mirrored set is not yet formatted/created. So the backup doesn't operate like a traditional backup in that regard - because you are effectively creating a new mirrored backup set. The link above will help explain that a bit more.

And seeing you ask this does lead me to ask the question of "What is the goal?" - This is a feature for a reason and it may just be a requirement you have. But it could also be that you are trying to handle something that may be better handled in other ways. For instance backing up to a device with redundancies built in, backing up to a network drive, backing up to a SAN that has proper SAN replication in place, grabbing a copy of a backup right after you take it, etc.

StackExchange Database Administrators Q#52728, answer score: 4