How to set up a Log Shipping Monitor (Transact-SQL)

How to set up a Log Shipping Monitor (Transact-SQL)

To set up a Log Shipping Monitor on an instance of SQL Server

Execute these stored procedures on the server running the instance of Microsoft® SQL Server™ 2000 that will monitor log shipping.

1. Run sp_add_log_shipping_primary to notify the monitor server which machine will be the primary in the log shipping pair. The output of the stored procedure will be the primary_id, which will be used by the sp_add_log_shipping_secondary stored procedure.
   
   
2. Run sp_add_log_shipping_secondary to notify the monitor server which machine will be the secondary in the log shipping pair.

Examples

This example sets up a log shipping monitor for an existing log shipping pair of the Northwind database. You will need to have set up log shipping using the Database Maintenance Plan Wizard prior to setting up this monitor manually. Note that a monitor is created during the wizard setup as well.

EXEC sp_add_log_shipping_primary
   @primary_server_name = 'MyPrimaryServer',
   @primary_database_name = 'Northwind',
   @maintenance_plan_id = '9B4E380E-11D2-41FC-9BA5-A8EB040A3DEF',
   @backup_threshold = 15,
   @threshold_alert = 14420,
   @threshold_alert_enabled = 1,
   @planned_outage_start_time = 0,
   @planned_outage_end_time = 0,
   @planned_outage_weekday_mask = 0

EXEC sp_add_log_shipping_secondary
   @primary_id = 1,
   @secondary_server_name = 'MySecondaryServer',
   @secondary_database_name = 'Northwind',
   @secondary_plan_id = 'B5C330FF-1081-4FCB-83D0-955DDFB56BA5',
   @copy_enabled = 1,
   @load_enabled = 1,
   @out_of_sync_threshold = 15,
   @threshold_alert = 14421,
   @threshold_alert_enabled = 1,
   @planned_outage_start_time = 0,
   @planned_outage_end_time = 0,
   @planned_outage_weekday_mask = 0,
   @allow_role_change = 0
GO

See Also

sp_add_log_shipping_primary

sp_add_log_shipping_secondary