2

I can backup to a local drive and mirror:

BACKUP DATABASE [xxx]
TO  DISK = N'XXXX.bak'
MIRROR TO DISK =N'Network path \XXX.bak'
WITH INIT

--directly to network drive 
BACKUP DATABASE [xxx]
TO  DISK = N'Network path \XXX.bak'
WITH INIT

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

Improve this question
1
  • 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
    Commented May 20, 2013 at 18:38

3 Answers 3

Reset to default
2

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.

BACKUP DATABASE [MGEC_STAGE] TO DISK = N'XXXX.bak' WITH INIT;
BACKUP DATABASE [MGEC_STAGE] TO DISK = N'Network path \XXX.bak' WITH INIT;

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
4
  • 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
    Commented Apr 25, 2013 at 13:11
  • what about using the second backup with the COPY_ONLY parameter?
    – KookieMonster
    Commented Apr 25, 2013 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
    Commented Apr 25, 2013 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
    Commented May 20, 2013 at 18:29
2

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
0

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 and acknowledge you have read our privacy policy.

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