SQL Backup 10

Log shipping to multiple standby servers

It is not possible to set up log shipping to multiple standby servers from the Log Shipping wizard. However, it can be set up by adapting the backup and restore scripts of an existing log shipping job as follows:

  1. For each of the standby servers, set up new network share locations, ensuring that the SQL Backup Agent service startup account on each server has permission to read and write from the share.
  2. Using the Log Shipping wizard, set up a log shipping job to the destination databases on your first standby server.
  3. Restore a full backup of the source database to the destination databases on the remaining standby servers, using WITH NORECOVERY or WITH STANDBY. You can use the SQL Backup Pro Restore wizard to do this. 
  4. Modify the backup job on the source server in SQL Server Management Studio:
    1. Open Object Explorer and expand SQL Server Agent
    2. Open the log shipping job and select the Steps page. 
    3. Click Edit and, for each of the remaining standby servers, add COPYTO="<network share location>" to the WITH clause.
  5. On each of the remaining standby servers, create a new SQL Server Agent job to restore the log backups based on the restore job on the first standby server:
    1. In SQL Server Management Studio, right click SQL Server Agent, then select New, then Jobs
    2. On the Steps page, add a new step.
    3. In the Command box, copy and paste the script from the restore job on the first standby server.
    4. Change the FROM DISK location to the appropriate network share, and the MOVETO location to a folder on the standby server.

Didn't find what you were looking for?