The other day I chatted with a gentleman who needed a second copy of his backup for a remote reporting system. Most of us need a copy or two of the backups, sometimes for a reporting or development refresh, or sometimes we want a second copy for safety reasons.
He would take the backup, and then copy it over to another system. I showed him that there’s actually a way to have SQL Server 2005 and higher (Enterprise Edition) make up to four backups at one time. All you have to do is use the MIRROR TO clause on the BACKUP command:
BACKUP DATABASE AdventureWorks
MIRROR TO DISK=’\\MyServer\MyShare\AdventureWorks.bak’;
When you use this command, remember that all the backup destinations have to be the same type (tape, disk, etc.), which isn’t normally a problem.