Copy backup to multiple network locations

• 
  Hi, thank you for your forum post.
  
  Yes you can copy to further network locations but you will need to set the second network location manually as the wizards in the GUI do not allow the configuration of a multiple network shares using the COPYTO keyword.
  
  For scheduled backup jobs:
  Open SSMS ->SQL Agent ->Jobs ->Locate the job in question ->Right Click Select properties ->Job Step ->Edit the job step and manually add the second network share as this simple example:
  
  DECLARE @errorcode INT
  DECLARE @sqlerrorcode INT
  EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [<Databasename>] TO DISK=''C:\Backups\<TYPE>_<DATABASE>_<DATETIME YYYYmmddhhnnss>.sqb'' WITH COPYTO = "\\TargetServer1\Folder1", COPYTO = "\\TargetServer2\Folder2", MAILTO = "support@red-gate.com", COMPRESSION = 3"', @errorcode OUT, @sqlerrorcode OUT;
  IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
  BEGIN
  RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
  END
  
  For one of backup using the backup wizard. On the final step of the wizard, select the scripts and copy the script into a new query window in SSMS and add the second COPYTO command (use the above code as a guide) and execute the query.
  
  Many Thanks
  Eddie

  Eddie D April 12, 2017 07:20

  - 0 +
• 
  Couple of questions.
  
  1) I imagine if i update the job in the future from the GUI after making the change that those changes would be overridden ?
  
  2) Is there any plans of adding this functionality to the GUI ?

  Coreythosaurus April 12, 2017 17:50

  - 0 +
• 
  Hi, I have the following answers to your questions:
  
  1) I imagine if i update the job in the future from the GUI after making the change that those changes would be overridden ?
  I have not tested this scenario, it is possible that any changes made to the Job via the Wizards in the GUI, will overwrite the manual change.
  
  From my experiences of using and supporting SQL Backup. If a job is modified outside of SQL Backup, you maybe not be able to edit the job using the GUI.
  
  2) Is there any plans of adding this functionality to the GUI ?
  
  Certainly not in the near future. I have added the details of this forum post to an existing feature request for SQL Backup GUI - Backup, Scheduled Backup Job and Log Shipping Wizards to support multiple COPYTO locations. The feature request reference is SB-4072. I will update if there is any change to this.
  

  Eddie D April 13, 2017 08:40

  - 0 +
• 
  Are there any other SQL Backup Users who would like to be able to configure multiple COPYTO locations using the Backup, Scheduled Backup Job and Log Shipping Wizards in the SQL Backup GUI to support multiple COPYTO locations?

  Eddie D April 13, 2017 08:43

  - 0 +
• 
  I would love to have this ability also as i currently have an automated job that refreshes prod data to multiple non prod environments.

  dlinge June 27, 2017 15:17

  - 0 +
• 
  +1 from me. I have just logged a call asking for the same. Thanks

  marmite_dBA June 30, 2017 13:49

  - 0 +
• 
  I go a step farther. I would like access to more of the standard job parameters from the wizard. For example, I like to have all jobs write to log files. We need some failed jobs to notify a SQL Agent Operator immediately (e.g. page or netsend).
  I would also like an easy way to manage the unwieldy job names. It would be nice to have a set up option for the Job name similar to the Backup file name. .
  

  ClearView September 27, 2017 17:07

  - 0 +
• 
  Old thread but I'd like to see this (Multiple copy locations) implemented also.  

  J_D December 18, 2019 09:40

  - 0 +
• 
  Would definitely like to see this feature implemented.

  mrwww76 March 11, 2020 11:38

  - 0 +

Add comment

Please sign in to leave a comment.