Backup Database to multiple locations simultaneously - Mirror Backups

Database backup is one the regular activity a DBA would perform. Some times you might come across a situation where in you need to backup the database to different location. When I say backup database to different locations, it means that a copy of backup file needs to be placed on a different location as well and this is different from the Split Backups.

This is can be achieved by different methods,

1. Take backup and then copy to multiple location
2. Take backup of the same database multiple times pointing to different locations
3. Use "MIRROR TO" Option in the Backup command 

Using the option "MIRROR TO" is very simple, you just need to mention "MIRROR TO" and "WITH FORMAT" options in the normal BACKUP DATABASE Statement and you are done. The backup database statement with these two options will take the backup of the same database to multiple locations at the same time.

This option "MIRROR TO" is introduced in SQL Server 2005 and this works only in SQL Server 2005 Enterprise Edition and later versions.

This can be used for all backup types and the Maximum number of "MIRROR TO" clauses that you can specify is three.

 

 

 

Example: 

BACKUP DATABASE DEMO TO DISK = 'C:\Backup\Demo_Full.bak' MIRROR TO DISK = 'D:\Mirror\Demo_Full.bak' WITH STATS=10, FORMAT BACKUP DATABASE Demo TO DISK = 'C:\Backup\Demo_Differential.bak' MIRROR TO DISK = 'D:\Mirror\Demo_Differential.bak' WITH STATS=10, DIFFERENTIAL, FORMAT BACKUP LOG Demo TO DISK = 'C:\Backup\Demo_log.trn' MIRROR TO DISK = 'D:\Mirror\Demo_log.trn' WITH STATS=10, FORMAT