Transact-SQL Reference

sp_add_log_shipping_secondary

Adds a secondary server to log_shipping_secondaries table.

Syntax

sp_add_log_shipping_secondary { [ @primary_id = ] primary_id }
    { , [ @secondary_server_name = ] 'secondary_server_name' }
    { , [ @secondary_database_name = ] 'seconday_database_name' }
    [ , [ @secondary_plan_id = ] secondary_plan_id ]
    [ , [ @copy_enabled = ] copy_enabled ]
    [ , [ @load_enabled = ] load_enabled ]
    [ , [ @out_of_sync_threshold = ] out_of_sync_threshold ]
    [ , [ @threshold_alert = ] 'threshold_alert' ]
    [ , [ @threshold_alert_enabled = ] threshold_alert_enabled ]
    [ , [ @planned_outage_start_time = ] planned_outage_start_time ]
    [ , [ @planned_outage_end_time = ] planned_outage_end_time ]
    [ , [ @planned_outage_weekday_mask = ] planned_outage_weekday_mask ]

Arguments

[@primary_id =] primary_id

Is the ID of the primary server. primary_id is int, with no default.

[@secondary_server_name =] 'secondary_server_name'

Is the name of the secondary server. secondary_server_name is sysname, with no default.

[@secondary_database_name =] 'seconday_database_name'

Is the name of the secondary database. secondary_database_name is sysname, with no default.

[@secondary_plan_id =] secondary_plan_id

Is the ID of the log shipping plan on the secondary server. secondary_plan_id is uniqueidentifier, with a default of NULL.

[@copy_enabled =] copy_enabled

Specifies whether the copy for the database is enabled on the secondary server. The default value of one (1) means the copy is enabled; zero (0) means copy is not enabled. copy_enabled is bit.

[@load_enabled =] load_enabled

Specifies whether the load for the database is enabled on the secondary server. The value of one (1), the default, means the load is enabled; zero (0) indicates it is not. load_enabled is bit.

[@out_of_sync_threshold =] out_of_sync_threshold

Is the length of time, in minutes, after the last load before an error is raised. out_of_sync_threshold is int, with a default of 60 minutes.

[@threshold_alert =] 'threshold_alert'

Is the error raised when the out-of-sync threshold is exceeded. threshold_alert is int, with a default of 14,421.

[@threshold_alert_enabled =] threshold_alert_enabled

Specifies whether an alert will be raised when an out-of-sync threshold is exceeded. The default value of one (1) means an alert will be raised; zero (0) means an alert will not be raised. threshold_alert_enabled is bit.

[@planned_outage_start_time =] planned_outage_start_time

Is the time in HHMMSS format that a planned outage begins. During a planned outage, alerts will not be raised if the out-of-sync threshold is exceeded. planned_outage_start_time is int, with a default of zero (0).

[@planned_outage_end_time =] planned_outage_end_time

Is the time in HHMMSS format that the planned outage ends. planned_outage_end_time is int, with a default of zero (0).

[@planned_outage_weekday_mask =] planned_outage_weekday_mask

Is the day of the week that a planned outage occurs. planned_outage_weekday_mask is int, with a default of zero (0). It can be one or more of the following values.

Value Day
1 Sunday
2 Monday
4 Tuesday
8 Wednesday
16 Thursday
32 Friday
64 Saturday

Return Code Values

0 (success) or 1 (failure)

Remarks

This procedure is used to add a secondary database to an existing primary database.

Permissions

Only members of the sysadmin fixed server role can execute sp_add_log_shipping_secondary.