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!
Discuss or comment on this article on our
Facebook group.