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?

  • 3
    @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 Shah May 20 '13 at 18:38

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
| improve this answer | |
  • 2
    "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
  • 2
    @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
  • 1
    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.

| 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.