add log shipping plan database

sp_add_log_shipping_plan_database

Adds a new database to an existing log shipping plan.

Syntax

sp_add_log_shipping_plan_database
    { [ @plan_id = ] plan_id | [ @plan_name = ] 'plan_name' }
    { , [ @source_database = ] 'source_database' }
    { , [ @destination_database = ] 'destination_database' }
    [ , [ @load_delay = ] load_delay ]
    [ , [ @load_all = ] load_all ]
    [ , [ @copy_enabled = ] copy_enabled ]
    [ , [ @load_enabled = ] load_enabled ]

Arguments

[@plan_id =] plan_id

Is the plan identification number to which the database will be added. plan_id is uniqueidentifier, with a default of NULL.

[@plan_name =] 'plan_name'

Is the name of the plan to which the database will be added. plan_name is sysname, with a default of NULL.

Note  Either the plan_id or the plan_name must be specified. Both cannot be specified at the same time.

[@source_database =] 'source_database'

Is the name of the database on the source server. source_database is sysname, with no default.

[@destination_database =] 'destination_database'

Is the name of the destination database. destination_database is sysname, with no default. The destination database must be unique in the log_shipping_plan_database table.

[@load_delay =] load_delay

Is the length of time in minutes to wait before loading the transaction log. load_delay is int, with a default of zero (0).

[@load_all =] load_all

Specifies that all newly copied transaction logs should be loaded when the job is run. If the value is set to zero (0), only one transaction log will be loaded when the job is run. If the value is one (1), all copied transaction logs will be loaded. load_all is bit, with a default of one (1).

[@copy_enabled =] copy_enabled

Specifies whether a copy for this database will be executed. copy_enabled is bit. The value of one (1) means a copy should be performed; zero (0) means no copy is made.

[@load_enabled =] load_enabled

Specifies whether a load of the transaction logs for this database should be performed. load_enabled is bit. The value of one (1) means a load should be performed; zero (0) means no load is performed.

Return Code Values

0 (success) or 1 failure

Permissions

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

Examples

Note this example assumes that the 'Pubs database backup' plan already exists.

EXECUTE   msdb.dbo.sp_add_log_shipping_plan_database
   @plan_name = N'Pubs database backup',
   @source_database = N'Pubs',
   @destination_database = N'pubs_standby',
   @load_delay = 60  –– wait an hour before loading the transaction logs