Copy backup to multiple network locations

Coreythosaurus

Can i copy the backup to multiple backup locations ? Id like to be able to write the backup to the local server, and then copy it to both a network share and DR ?

Eddie D

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

Coreythosaurus

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 ?

Eddie D

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

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?

dlinge

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

marmite_dBA

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

ClearView

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. .

J_D

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

mrwww76

Would definitely like to see this feature implemented.