mirrored backups and log ship

mliumliu Posts: 26
edited November 12, 2006 7:54PM in SQL Backup Previous Versions
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.
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Could you pls explain your requirements? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • mliumliu Posts: 26
    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.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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] " '
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • mliumliu Posts: 26
    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.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.