sp_add_maintenance_plan

Transact-SQL Reference

Transact-SQL Reference

sp_add_maintenance_plan

Adds a maintenance plan and returns the plan ID.

Syntax

sp_add_maintenance_plan [ @plan_name = ] 'plan_name' ,
    @plan_id = 'plan_id' OUTPUT

Arguments

[@plan_name =] 'plan_name'

Specifies the name of the maintenance plan to be added. plan_name is varchar(128).

@plan_id = 'plan_id'

Specifies the ID of the maintenance plan. plan_id is uniqueidentifier.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_add_maintenance_plan must be run from the msdb database and creates a new, but empty, maintenance plan. To add one or more databases and associate them with a job or jobs, execute sp_add_maintenance_plan_db and sp_add_maintenance_plan_job.

Permissions

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

Examples

Create a maintenance plan called Myplan.

DECLARE   @myplan_id UNIQUEIDENTIFIER
EXECUTE   sp_add_maintenance_plan N'Myplan',@plan_id=@myplan_id OUTPUT
PRINT   'The id for the maintenance plan "Myplan" is:'+convert(varchar(256),@myplan_id)
GO

Success in creating the maintenance plan will return the plan ID.

'The id for the maintenance plan "Myplan" is:' FAD6F2AB-3571-11D3-9D4A-00C04FB925FC