mirrored backups and log ship

mliu

Does anyone know whether mirrored backups will work with log shipping? It looks like it only works if the backup is performed. Any ideas as to when this will become available to log shipping as well if that's the case?

Thanks.

petey

Could you pls explain your requirements? Thanks.

mliu

Hi Peter,

Our requirement is to see whether there will be a way to copy the logs onto a different machine while it does the log shipping. Let's say we have a source servern and a destination server, and we are doing the log shipping from the source to destination. Currenly, it is only possible for the desitnation server to get the logs from the source within your software.

What we are looking for is to see whether there will be a way to copy the logs onto a third server. This is the similar backup strategy that you currently have in mirrored backups when log shipping is not used where it can actually copy the backup files onto different machines/disks simulteneuously.

Thanks.

petey

Use multiple COPYTO options e.g.

master..sqlbackup '-sql "BACKUP LOG sqlprod TO DISK = [F:\Backups\<AUTO>] WITH COPYTO = [\\netshare1\sqlprod\logs], COPYTO = [\\netshare2\sqlprod\logs], COPYTO = [\\netshare3\sqlprod\logs] " '

mliu

Hi Peter,

Thanks for your quick response.

Does this mean that we have to manually modify the job SQL Bakcup creates? Or is there an option to do so? I didn't see it...

Thanks.

petey

Yes, you have to make some changes to the scripts manually.

On the primary server, use Enterprise Manager/SSMS to edit the job step that performs the backup. Add additional COPYTO options to copy the backup file(s) to the other standby server(s). Ensure that the SQL Backup service user has rights to those network shares.

On the additional standby server(s), you need to create a SQL Server Agent job to perform the restores. You can use the script from the first standby server, and modify the parameters to fit your needs.