Using WITH MIRROR to Copy Your Backups

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:


TO DISK='C:\SQLServerBackups\AdventureWorks.bak',
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.

Skip to main content