Sign up ×

I can backup to a local drive and mirror:

TO  DISK = N'XXXX.bak'
MIRROR TO DISK =N'Network path \XXX.bak'

--directly to network drive 
TO  DISK = N'Network path \XXX.bak'

The mirror option is not available in Standard Edition (SQL Server 2012), so what's the best approach to resolve the issue?

share|improve this question

migrated from Apr 25 '13 at 11:11

This question came from our site for professional and enthusiast programmers.

@user2315732 What are you trying to achieve by doing this ? Backing up directly to Network Path will cause all sort of troubles - what happens if your n/w goes down while the backup is running, if you experience a n/w glitch along with backup time will become almost double or more !! Instead do a backup locally on the server and then transfer it to a n/w location - this way you will save time in backing up database, get away from troubles like n/w glitch or n/w down. –  Kin May 20 '13 at 18:38

3 Answers 3

up vote 2 down vote accepted

As the documentation shows, MIRROR TO is only available in the Enterprise editions of SQL Server 2005+.

If your end goal is only to have a redundant backup for safekeeping in a different location from the local disk, and they don't have to be the same backup, then you could just do them in sequence in a single batch.


If the backups have to be identical, then you will just have to perform the backup to the local drive and copy it, using any of (among other options)

  1. xp_cmdshell and the xcopy/copy command
  2. Windows Task Scheduler sqlcmd command line and xcopy/copy, assuming you (i) are performing the backup using a schedule (e.g. SQL Agent) and (ii) know how long it will take, timing the copy to occur soon after
  3. SSIS package, along the lines of the Windows Task Scheduler
share|improve this answer
"Timing the copy to occur soon after" is ridiculously unwise. If SQL Server Agent is used to backup the database, then why not just have the next step (only on success, of course) perform the copy? –  Aaron Bertrand Apr 25 '13 at 13:11
what about using the second backup with the COPY_ONLY parameter? –  KookieMonster Apr 25 '13 at 13:17
@KookieMonster that is probably wise if the first backup is being relied upon for log chain, diffs, etc., but it still doesn't prevent it from being a different backup (i.e. it is not a copy of the previous backup, as your question might be interpreted to imply). –  Aaron Bertrand Apr 25 '13 at 13:25
And, of course, it would cause a second pass through the entire database - potentially a lot of I/O. –  Thomas Rushton May 20 '13 at 18:29

An alternative is to use software that will allow for simultaneous backups to more than one location, such as Quest's Litespeed for SQL Server, or Red-Gate's SQL Backup. Both will allow for multiple copies to be created.

share|improve this answer

Personally, I'd use SQL to do a local dump; with compression enabled [better]. Then I'd use Robocopy to copy the [smaller] file to the network location [faster]. This is pretty much what Robocopy is designed to do [stronger].

The backup should be striped across a number of files to allow maximum write speed possible: Backup database X to disk = 'd1.bak',disk='d2.bak',disk='d3.bak',disk='d4.bak' Number is generally CPU count...

Robocopy can be fine-tuned to respect the network, or thrash it (copying all the files at the same time using multiple threads)... so you can get the files of the server fast.

share|improve this answer

Your Answer


