Tuesday, September 27, 2011

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,

Take backup and then copy to multiple location

Take backup of the same database multiple times pointing to different locations

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:

BACKUPDATABASEAdventureWorks

TODISK='C:\Backup\AdventureWorks_Full.bak'

MIRRORTODISK='C:\Mirror\AdventureWorks_Full.bak'

WITHSTATS=10,FORMAT

BACKUPDATABASEAdventureWorks

TODISK='C:\Backup\AdventureWorks_Differential.bak'

MIRRORTODISK='C:\Mirror\AdventureWorks_Differential.bak'

WITHSTATS=10,DIFFERENTIAL,FORMAT

BACKUPLOGAdventureWorks

TODISK='C:\Backup\AdventureWorks_log.trn'

MIRRORTODISK='C:\Mirror\AdventureWorks_log.trn'

WITHSTATS=10,FORMAT

When it comes to restoring the database, we can use either of the backup copies to restore or recover the database.