Creates a new log shipping plan. Inserts a row in the log_shipping_plans table.
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
[@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)
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.
Only members of the sysadmin fixed server role can execute sp_add_log_shipping_plan.
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