Daily database maintenance using SQL Server 2008 and a stored procedure

Question

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);

cnn.Open();
planid = (string)cmd.ExecuteScalar();
cnn.Close();

//   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);

cnn1.Open();
cmd1.ExecuteNonQuery();
cnn1.Close();

Answer 1

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.

Answer 2

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