Red Gate forums :: View topic - Network Copy to Multiple locations
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Network Copy to Multiple locations

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
olafdedig



Joined: 10 Jan 2013
Posts: 1

PostPosted: Thu Jan 10, 2013 8:35 am    Post subject: Network Copy to Multiple locations Reply with quote

Hello
I am using SQL back up 7 to do our backups, part of that is we use the copy to network location functionality. Is it possible to copy the backup to more than one location within the same backup task ? for example do backup then copy to location \\servername\dbbackups\ and the copy to \\otherservername\dbbackupsThanks
Back to top
View user's profile Send private message
Marianne



Joined: 03 Oct 2011
Posts: 45

PostPosted: Thu Jan 10, 2013 9:54 am    Post subject: Reply with quote

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:
Code:
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
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 921
Location: Red Gate Software

PostPosted: Thu Jan 10, 2013 10:28 am    Post subject: Reply with quote

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:
Code:

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
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group