4

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

migrated from stackoverflow.com Jun 30 '12 at 3:02

This question came from our site for professional and enthusiast programmers.

  • 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 '12 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 '12 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. – Mark Storey-Smith Jul 2 '12 at 23:08
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 '12 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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