SQL Server 2005 and newer allows you to create backup files in 2
or more locations using the MIRROR TO option. The
disadvantage of this is that the backup process will only complete
once all the backup files have been completely written to, and the
backup duration will be longer compared to just backing up to a
single file. This increases the window of failure.
Should a hardware failure occur during the backup process,
all the backup files will be unusable.
SQL Backup provides the option to copy a backup file to one or
more other directories once a backup has completed. Only a
single backup set is created, and that is the file that gets
copied. The backup duration is shorter, hence decreasing the
probability that the backup process will fail.
You perform the copying using the COPYTO option. For e.g.
the following command copies the backup file to the
'\\backupnode\sqlbackups\' remote share once the AdventureWorks
database has been backed up.
You can use multiple COPYTO options too e.g.
That has to be the easiest way ever to copy a backup file to
other directories!
Document history
7/4/2010 | Initial release. |