Backup to multiple locations at the same time (multiple copies of the backup files)

Sometimes it can be usefull to have two ore more copies of the same backup (e.g. you have two locations/disks and want a copy in/on both)

 

So lets explain how to backup to two locations at once. First the bad news this is an Enterprise (SQL Server 2005, 2008 and 2008R2) and Datacenter Edition (SQL Server 2008R2) only feature.

 

If the backup to one location looks like this

BACKUP DATABASE MyDatabase
     
TO DISK = 'D:\BACKUP\MyDatabase.bak'

 

Backing up to two locations  looks like this

 

BACKUP DATABASE tg
      TO DISK = 'D:\BACKUP\MyDatabase.bak'
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup2.bak'
      WITH FORMAT

The WITH FORMAT is required even if the backup files do not exist

 

You can go wild and backup to up to 8 locations at the same time.

BACKUP DATABASE tg
      TO DISK = 'D:\BACKUP\MyDatabase.bak
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup2.bak'
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup3.bak'
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup4.bak'
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup5.bak'
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup6.bak'
      MIRROR
      TO DISK = 'E:\BACKUP\MyDBBackup7.bak'
      MIRROR
     
TO DISK = 'E:\BACKUP\MyDBBackup8.bak'
      WITH FORMAT

 

I would not recommend to backup to 8 locations thats because the feature does not work as you might expect. It works like any other transaction in the database all or nothing. This means if one of your locations is unavailable the backup does not start and if one location fails during the backup the whole backup operation fails.