Sql-server – How to backup to local drive and network drive simultaneously

backupsql serversql-server-2012

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?

Best Answer

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
Related Question