Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Apr 25 '13 at 11:11

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

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 May 20 '13 at 18:38
add comment

2 Answers

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.

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
share|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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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