MS SQL 2008 R2, issue: impossible to Modify a Maintenance Plan

Some time ago, I wrote an article about upgrading a Microsoft SQL Server 2008 R2 Express edition to a full Standard Edition. Today I discovered that the transaction log of the vCenter Server database (in my homelab) has grown and needed some maintenance. VMware published KB 1003980 “Troubleshooting transaction logs on a Microsoft SQL database server” on this subject. However It is not my intention to go for a Simple recovery Model. So I decided to create a Maintenance Plan. After creating a Maintenance Plan, I discovered that it is not possible to modify an existing Maintenance Plan. There is an Modify option, but it just does not work.

This post on a Microsoft forum set me on track on solving this issue. The root cause is that before upgrading to the Standard Edition, the Microsoft SQL Server Management Studio tools were installed in a Basic edition, where the Complete Tools should be installed.

So now, the remaining question is how to replace the Basic edition for a complete edition? In the rest of this post, I will show you how it is done. I have added some screenshots, I will only show the critical dialog windows and leave out the Next, next windows.

So mount the MS SQL server installation media. First I will show how to check which version of the Management Tools is currently installed. Select the Tools section and choose “Installed SQL Server features discovery report

After a few moments, the result shows up in IE. We definitely have the Basic version installed.

First we must uninstall the Basic version. Go to “Programs and Features“, select “Microsoft SQL Server 2008 R2 (64-bit)” (that is the version that is installed) and choose “Uninstall/Change”.

From the next dialog, choose Remove. Do not be afraid.

Click OK in the “Setup Support Rules” dialog.

The next screen “Select Instance”. In my case, there is only one instance installed, so proceed with “Next”.

In the “Select Features” dialog, tick “Management Tools – Basic” and proceed.

Click “Next” in the “Removal Rules” dialog.

Click “Remove” in the “ready to remove” dialog.

If everything goes well, it will end with this message.

Now it is time to install the Complete version. Start the installer, go to the Installation section and choose the “New installation or add features to an existing installation” option.

Click “Install” in the “Setup Support Files” dialog.

Click “Next” in the “Setup Support Rules” dialog.

In the “Installation Type” dialog, choose “New installation or add shared features”.

Click “Next” in the “Product Key” dialog.

In the “License terms dialog”, place a tick at “I accept the license terms” and proceed with Next.

In the “Setup Role”, select “SQL Server Feature Installation” and proceed with Next.

In the “Feature Selection” dialog, make sure that “Management Tools – Complete” is slected. This wil also select “Management Tools – Basic” and proceed with Next.

In “Installation Rule” dialog, Next.

The next dialog is “Disk space requirements”, should be no problem and continue. The same goes for the “Error Reporing”, “Installation Configuration Rules” and “Ready to Install”,  dialog. The whole process should finish with this window.

So let’s see if this action was successful. Open the Microsoft SQL Server Management Studio and try to Modify an existing Manintenance Plan. Here is the result, we are now able to modify the Maintenance Plan. By the way, this plan was created using the Maintenance Plan Wizard.

Thank you very much for reading. I welcome your feedback.

Advertisements

71 Responses to MS SQL 2008 R2, issue: impossible to Modify a Maintenance Plan

  1. […] Update 15-05-2011: There is at leat one caveat after upgrading to the Standard Edition, please read this post. […]

  2. Andrew says:

    thank you!

  3. gpgeminiGuy says:

    Thanks, worked great for me !

  4. cris says:

    IS possible to open the existing Maintenance plans from the sql server in the visual studio?

    • paulgrevink says:

      Hello Chris,

      I am not sure if you can open a maintenance plan in Visual Studio. I do only use the SQL Management Studio.

      Regards,
      Paul

  5. Radu says:

    Thank you!

  6. Solved my problem, Thanks!

  7. msavx says:

    Thanks for the post, it saved me a lot of trial and error.

  8. Todd says:

    This works for MS SQL 2008 NOT R2 also. Thanks!

  9. Benson says:

    Thanks a million on this, i’m not sure what the logic behind MS’s thinking here.. if they’re going to do this, why not just not have the maintenance plan as a check ..why separeate Basic vs Complete.. soooooooooooooooooo confusing MAN!!!

    to the Author, Thanks so much! this would’ve cost about $200 – $300 for MS Support.

  10. Scott Newman says:

    I really appreciate your post and instructions. As Benson said, this saved a lot of money on MS support. I just installed 2008 x64 Standard Edition for the first time in our environment, so I had no idea if this required some other special tweak versus 2005. I’m glad the fix was so simple and quickly accomplished.

    Thanks a bunch for sharing this with everyone in the greater community!!

  11. Michael Woudstra says:

    Thanks for the clear explenation. Just to be sure, if there is already a maintenance plan in the Basic version. It will be still available after uninstalling the basic version and the installing the complete version? And it will not affect the databases configurations?

    Thanks for the info

    • paulgrevink says:

      Hello Michael,

      Thanks for the feedback. Answering your question, I am not sure. In my case, the MS SQL server had just 2 databases, belonging to a VMware vCenter installation and no Maintenance Plans. After upgrading from Express to Standard version, I had no problems re-connecting the vCenter Server. If your database server is production environment, I would recommend to do a test first. Is your server virtual, create a clone and practice the upgrade.

      If you have performed the upgrade, I would really appreciate your feedback on this subject,

      Kind regards,

      Paul

  12. Luke says:

    Thank-you very much! Worked like a charm for Server 2008 R2. All maintenance plans were still in place after making the uninstalling the basic version and reinstalling the complete version.

  13. James says:

    Thanks for taking the time to publish this information. I appreciate it. Was nervous as hell, but it worked great.

  14. rcg26 says:

    This is a great write up. We upgraded from SQL Server Express to Standard 2008 R2 on a VPS where our website is hosted and I couldn’t modify my database backups… I was nervous too, as I did the steps on a production box, but it all went well and modifying plans in SSMS works as expected now. Thanks!

  15. rcg26 says:

    This is a great write up. I performed these steps on a production box that was upgraded from SQL Server Express to Standard 2008 R2 but was still using the basic tools. Everything went well and I”m now able to modify my management plans in SSMS. Thanks!

  16. elena says:

    Thanks a lot. Very detailed and very useful

  17. It works for me! Thanks a lot.

  18. elaine marshall says:

    Thank you. This worked great…easy to follow and understand.

  19. Gavin Buke says:

    Wow, finally fixed thanks!!

  20. Ludwig says:

    Thank you! This was the solution, I needed.

  21. dbaeyes says:

    Thanks Paul! This article solved the riddle for me. I appreciate your digging into it and publishing the solution!

  22. Anders says:

    Thanks for sharing.

  23. Dave says:

    Thanks you are a godsend!!!!!

  24. Nick says:

    Thank you!! Totally solve my problem!

  25. Axel says:

    Great, many thanks !!! Very detailed manual how to solve this problem. Great job !!!

  26. yan d says:

    Thank you, worked like a charm at first try !

  27. gabriel says:

    thanks man!
    you saved me lot of time

  28. Jernej says:

    Hey!
    Thanks…. Works perfect… 2008 R2

  29. Kuvaldon says:

    Yep! Good advice!

  30. Stoffe says:

    Thank you for the guide. It worked perfectly!

  31. Simon says:

    Thanks – that solved my problem and I appreciated how easily you explained he process

  32. Robb says:

    Thanks. Worked for me.

  33. Dave G. says:

    Awesome step-by-step article! I tried this on SQL Server 2012 and it worked and I can now edit maintenance plans. Thanks!

  34. Jerrod B says:

    Thank you, thank you, thank you!

  35. Nice, this fixed my problem too (2008 R2). Happened b/c of the same thingy you described. Upgrading Express to Standard while back. I just noticed I couldn’t mod any of my maint plans.

    Thanks for posting this, and keep it up. it’s still helping us when we google search about it!

  36. Hakan says:

    This solved my very similar problem on a SQL 2008 server where after service pack 3 or some Windows update we could not manage maintenance jobs any more.

  37. Alissa says:

    AWESOME – thanks so much!

  38. unclesausage says:

    Nice walk thru, helped heaps. Thanks.
    A+++ poster 😉

  39. Rod Carsten says:

    Thank you for the very clear instructions. The SQL I was working with was on a SBS and had been previously installed. I was baffled and your post solved the problem.

  40. Goshedo says:

    Thanks a lot! It worked.

  41. Mrsnrub says:

    Brilliant, thanks for this. It was very frustrating not being able to modify a Maintenance plan and not knowing why. This worked a treat.

  42. Thanks .. It worked and was much appreciated !!

  43. Mike says:

    Hey Paul, Mike here, your old collegue of LogicaCMG? Great article! Helped me out here! Thanks. Cheers, Mike

  44. Jens Kruse says:

    Thanks, that did help! Regards, Jens

  45. Davide Pezzi says:

    Thank you very much!

  46. joseph says:

    Thank you. it was exactly what I needed and succeeded to run maintenance plan. You are a genius!

  47. I was on the verge of tears before I found your article. Thank you ever so much for taking the time to post this.

  48. Ken says:

    Thanks for the detailed steps, exactly what I was looking for and worked a treat.

  49. Russ says:

    Thanks, solved the problem here, much appreciated!

  50. Rimantas says:

    Very helpful ! Many thanks …

  51. Dina says:

    Thank you!!

  52. Jake says:

    How will this affect data? Will it be picked up by the new installation?

  53. Bob Roberts says:

    Fixed my boat! Thank you!

  54. Mohammed Atef says:

    Thanks to you very much for this

  55. Teo says:

    thank you, fixed.

  56. mattsoule says:

    Flawless technical write-up. Thank you, Sir!

  57. Greg LaViolette says:

    Bless you.

  58. Olsen says:

    great work, thx a lot !!

  59. Janusz says:

    It worked for me too :-)\
    I was affraid to run remove utility, but everything was OK. Thanks again

  60. Zach T says:

    Thanks for this blog post. I found that the removal step was not necessary and I skipped to the installation step without issue.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: