Copy backup to multiple network locations

Coreythosaurus · April 12, 2017 5:01AM

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 · April 12, 2017 7:20AM

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 · April 12, 2017 5:50PM

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 · April 13, 2017 8:40AM

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 8:43AM

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 · June 27, 2017 3:17PM

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 · June 30, 2017 1:49PM

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

ClearView · September 27, 2017 5:07PM

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 · December 18, 2019 9:40AM

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

mrwww76 · March 11, 2020 11:38AM

Would definitely like to see this feature implemented.

Copy backup to multiple network locations

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk