Can we take backup of a database in two locations at a time?

Can we take backup of a database in two locations at a time?

Yes we can.

In a typical scenario DBA will need to maintain backup of a single database in multiple locations where one file is needed for restore to one instance and the other file is needed for another instance restore. When a backup file is needed to be used for restore in 2 instances we can perform using single backup file as well but obviously while the first instance is being restored the other instance need to wait till the restore completes. If the database size is not huge (in MB’s) then the wait of 2^nd instance will be negligible but if the database size is huge then the 2^nd instance need to wait for long time for the first instance restore to complete. To avoid this waiting time if we have 2 copies of same backup file both the files can be used to trigger the restore in the 2 instances at a time.

In SQL Server there is a well-known concept called MIRRORING of backups. This concept of MIRROR can be used to take the backup of a single database through a single backup command in 2 different locations.

Syntax:

BACKUP DATABASE <Database Name> TO

DISK = 'Loc1
MIRROR TO DISK = 'Loc2
WITH FORMAT, STATS = 10, INIT

Example:

BACKUP DATABASE AdventureWorks TO

DISK = 'D:\SQL2K8\Backups\AdventureWorks1.BAK'
MIRROR TO DISK = 'E:\SQL2K8\Backups\AdventureWorks2.BAK'
WITH FORMAT, STATS = 10, INIT

The above command takes backup of ‘AdventureWorks’ database at a time in the two locations ‘D:\SQL2K8\Backups\’ and ‘E:\SQL2K8\Backups\’. Any one of these backups can be used to restore the ‘AdventureWorks’ database in other instance.

Thanks!!