Saturday, December 29, 2007

Mirrored Backups - Take Multiple Copies of Backups With a Single Command [SQL Server 2005]

SQL Server 2005 BACKUP DATABASE statement allows mirroring of backup files to multiple media and locations. Mirroring a database backup increases reliability and availability of backups by creating multiple copies.

For example the following statement takes backup of the the database Northwind to two different locations D:\DataabseBkps_Set1 and E:\DataabseBkps_Set2.


TO DISK = 'D:\DataabseBkps_Set1\Northwind.bak'


TO DISK = 'E:\DataabseBkps_Set2\Northwind.bak'


If backup in one location corrupts, you can access the another backup. So you are creating a backup to backup.

The MSDN description on MIRROR TO clause says

Specifies a set of one or more backup devices that will mirror the backups devices specified in the TO clause. The MIRROR TO clause must be specify the same type and number of the backup devices as the TO clause. The maximum number of MIRROR TO clauses is three.

This option is available only in SQL Server 2005 Enterprise Edition and later versions.