sp_add_log_shipping_plan

Creates a new log shipping plan. Inserts a row in the log_shipping_plans table.

Syntax

sp_add_log_shipping_plan [ @plan_name = ] 'plan_name' ,
    [ @description = ] 'description' ,
    [ @source_server = ] 'source_server' ,
    [ @source_dir = ] 'source_dir' ,
    [ @destination_dir = ] 'destination_dir' ,
    [ @history_retention_period = ] history_retention_period ,
    [ @file_retention_period = ] file_retention_period ,
    [ @copy_frequency = ] copy_frequency ,
    [ @restore_frequency = ] restore_frequency ,
    [ @plan_id = ] plan_id OUTPUT

Arguments

[@plan_name =] 'plan_name'

Is the name of the plan. The name must be unique and cannot contain the percent (%) character. plan_name is sysname, with no default.

[@description =] 'description'

Is the description of the plan. description is nvarchar(500), and the default is NULL.

[@source_server =] 'source_server'

Is the name of the source server. source_server is sysname.

[@source_dir =] 'source_dir'

Is the full path to the directory from which the transaction log files will be copied. source_dir is nvarchar(500).

[@destination_dir =] 'destination_dir'

Is the directory in which the transaction log is to be copied. destination_dir is nvarchar(500).

[@history_retention_period =] history_retention_period

Is the length of time in minutes in which the history is retained in the log_shipping_history table before deletion. history_retention_period is int, with a default of 2,880 minutes (two days).

[@file_retention_period =] file_retention_period

Is the length of time in minutes in which the transaction log files are stored on the secondary server before deletion. file_retention_period is int, with a default of 2,880 minutes (two days).

[@copy_frequency =] copy_frequency

Is the frequency in minutes in which the plan is copied. copy_frequency is int, with a default of five minutes.

[@restore_frequency =] restore_frequency

Is the frequency in minutes in which the restore job for this plan takes place. restore_frequency is int, with a default of five minutes.

[@plan_id =] plan_id OUTPUT

Is the plan identification number assigned to the plan that was created successfully. plan_id is an output variable of type uniqueidentifier, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_add_log_shipping_plan also can be used to create two jobs to perform the copy and load for this plan.

After sp_add_log_shipping_plan executes successfully, sp_add_log_shipping plan_database can be executed to add databases to the plan.

Permissions

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

Examples

EXEC   msdb.dbo.sp_add_log_shipping_plan
   @plan_name=N'Pubs database backup'
   @description= N'Log shipping the pubs database',
   @source_server= N'my_source',
   @source_dir= N'\\my_source\pubs_logshipping',
   @destination_dir= N'c:\logshipping\pubs',
   @history_retention_period= 60,  -- 1 hour
   @file_retention_period= 1440,  -- 1 day
   @copy_frequency= 10,  -- copy files every 10 minutes
   @restore_frequency= 30  -- load files every 30 minutes