Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.