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.
Print article | This entry was posted by =tg= on 2011-01-25 at 11:23:34 . Follow any responses to this post through RSS 2.0. |