I have created a database maintenance plan using wizard though that link for SQL Server 2005.

I have created a Maintenance Plan for SQL Server 2008 and it works fine.

Now I required to complete the same task using a stored procedure for my desktop application.

I have tried this stored procedure for that but it doesn't works for me.

Stored Procedure Execute Successfully and also i can see the entered values in the table sysdbmaintplans of msdb the system database but created maintenance plan doesn't shows Under tree(MANAGEMENT=>MAINTENANCE PLAN) of Object Explorer(SQL Server Management Studio).

Please Help...

Below is the code snippet I have tried.

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

String planid = "";

string maintenancePlan = "MyMaintenance";

string SQLstr = "DECLARE @plan_id as Varchar(50)";
SQLstr += " EXEC sp_add_maintenance_plan ";
SQLstr += " @plan_name='" + maintenancePlan + "',";
SQLstr += " @plan_id= @plan_id OUTPUT";
SQLstr += " SELECT @plan_id";

SqlConnection cnn = new SqlConnection(MSDBconnStr);
SqlCommand cmd = new SqlCommand(SQLstr, cnn);

planid = (string)cmd.ExecuteScalar();

//   sp_add_maintenance_plan_db [ @plan_id = ] 'plan_id' , 
//[ @db_name = ] 'database_name'

string maintenancePlanDataBase = "MyDataBase";
string SQLstr1 = " EXEC sp_add_maintenance_plan_db ";
SQLstr1 += " @plan_id='" + planid + "',";
SQLstr1 += " @db_name='" + maintenancePlanDataBase + "'";

SqlConnection cnn1 = new SqlConnection(MSDBconnStr);
SqlCommand cmd1 = new SqlCommand(SQLstr1, cnn1);

  • This is really a question for the DBA site, but what exactly are you trying to do? Are you trying to create a maintenance plan from a desktop application? If so, why not just use SSMS?
    – Pondlife
    Jun 29, 2012 at 13:46
  • i have a task to provide a functionality to create maintanace plan of database for daily database backup from my desktop application....because as per my leaders instruction we required to bypass the requirment of SSMS at client side(user side) because user may not have the knowledge of SSMS.......THANK YOU SO MUCH....
    – Pritesh
    Jun 29, 2012 at 15:43
  • A user that doesn't understand SSMS should not be creating database maintenance plans. That would likely include whoever asked you to do this work. Jul 2, 2012 at 23:08

2 Answers 2


Instead of using maintenance plans, check out Ola Hallengren's free maintenance stored procedures. They're way more powerful than maintenance plans and they don't require SSMS (per your requirements) to implement. You can set them up with any T-SQL connection.

  • ya exactly i have used this stored procedure......thank you so much for replying
    – Pritesh
    Jul 3, 2012 at 11:14

To specifically address your question. It sounds like your desktop version is greater than SQL 2008 and the plan is most likely hidden in the SSIS structure. (and sp_add_maintenance_plan_db is probably deprecated). I'd try the following with your plan id to expose the plan in your newer version of SQL Server. (Please let us know what SQL Serve version that's on your desktop).

EXECUTE   sp_add_maintenance_plan N'Myplan',@plan_id=@myplan_id OUTPUT  

reference: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-maintenance-plan-transact-sql?view=sql-server-ver15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.