Copy backup to multiple network locations

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 ?

Comments

  • Eddie DEddie D Posts: 1,686 Rose Gold 5
    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 = "[email protected]", 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 Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • 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 DEddie D Posts: 1,686 Rose Gold 5
    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 Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • Eddie DEddie D Posts: 1,686 Rose Gold 5
    edited April 13, 2017 9:26AM
    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 Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • 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_dBAmarmite_dBA Posts: 3 Bronze 1
    +1 from me. I have just logged a call asking for the same. Thanks
  • ClearViewClearView DFWPosts: 12 Bronze 1
    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_DJ_D Posts: 2 New member
    Old thread but I'd like to see this (Multiple copy locations) implemented also.  
  • mrwww76mrwww76 Posts: 2 New member
    Would definitely like to see this feature implemented.
Sign In or Register to comment.