Network Copy to Multiple locations

Comments

• Options

  Marianne Posts: 55

  January 10, 2013 3:54AM

  Hello,
  
  Yes it is possible to copy a backup to more than one location. You will need to edit the backup script, as this option is not available in the Back Up or Schedule Backup Jobs wizards. You can copy the script from the last step of the wizard, then edit and run it from SQL Server Management Studio.
  
  To copy a backup to another location, add COPYTO = '\\location\backups' to the WITH clause of the BACKUP command. You can include multiple COPYTO options to a BACKUP command. For example:

  execute master..sqlbackup '-sql "BACKUP DATABASE ''AdventureWorks'' TO DISK = ''c:\Backups\<AUTO>.sqb'' WITH COPYTO = ''\\servername\dbbackups'', COPYTO = ''\\otherservername\dbbackups'' " '
  

  
  The SQL Backup Agent service must have permission to write to the COPYTO locations.
  
  More information on the COPYTO option is available here: http://www.red-gate.com/supportcenter/Content/SQL_Backup/help/7.2/SBU_BACKUP_cmd
  
  I hope that helps.
  
  Cheers,
  
  Marianne

  Marianne Crowder
  Red Gate Software Limited

  0 · Share on Twitter
• Options

  Eddie D Redgate › Posts: 1,781 Rose Gold 5

  January 10, 2013 4:28AM

  Hi, thank you for your forum post.
  
  It is most definitely possible for SQL Backup to use multiple COPYTO parameters.
  
  However you cannot configure this using the wizards available through the SQL Backup GUI.
  
  If using the backup wizard you will need to script out the backup task to a new query window and manually add the second COPYTO comand. You can obtain the backup script in the last step of the Backup wizard via the script tab.
  
  Or if you wish to modify a scheduled backup job, open SSMS ->SQL Agent ->Jobs ->locate the job SQL backup created ->Right Click and select properties ->Steps->Edit the job step and manually add the second COPYTO command.
  
  Below, I have included Backup job syntax with two COPYTO parameters for reference, my example uses the code from a scheduled backup job:

  DECLARE @exitcode int
  DECLARE @sqlerrorcode int
  EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [AdventureWorks] TO DISK = ''E:\Backups\<AUTO>.sqb'' WITH FILEOPTIONS = 5, ERASEFILES = 2b, ERASEFILES_REMOTE = 5b, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4, COPYTO = ''\\server1\CopyToFolder1'', COPYTO = ''\\Server2\CopyToFolder2'', INIT, THREADCOUNT = 3, VERIFY"', @exitcode OUT, @sqlerrorcode OUT
  IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
  BEGIN
  RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
  END
  

  
  I hope the above answers your question.
  
  Many Thanks
  Eddie

  Eddie Davis
  Senior Product Support Engineer
  Redgate Software Ltd
  Email: support@red-gate.com

  0 · Share on Twitter