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.
[…] Update 15-05-2011: There is at leat one caveat after upgrading to the Standard Edition, please read this post. […]
thank you!
Thanks, worked great for me !
IS possible to open the existing Maintenance plans from the sql server in the visual studio?
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
Thank you!
Solved my problem, Thanks!
Thanks for the post, it saved me a lot of trial and error.
This works for MS SQL 2008 NOT R2 also. Thanks!
Hi Todd,
Thanks for your feedback.
Regards,
Paul
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.
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!!
Hi Scott,
Thans for your feedback. I really appreciate it.
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
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
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.
Hi Luke,
Thanks for your feedback. Glad I could help.
Regards,
Paul
Thanks for taking the time to publish this information. I appreciate it. Was nervous as hell, but it worked great.
Hi James,
Thanks for the feedback. Glad, I was able to help.
Regards,
Paul
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!
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!
Thanks a lot. Very detailed and very useful
It works for me! Thanks a lot.
Thank you. This worked great…easy to follow and understand.
Wow, finally fixed thanks!!
Thank you! This was the solution, I needed.
Thanks Paul! This article solved the riddle for me. I appreciate your digging into it and publishing the solution!
Thanks for sharing.
Thanks you are a godsend!!!!!
Thank you!! Totally solve my problem!
Great, many thanks !!! Very detailed manual how to solve this problem. Great job !!!
Thank you, worked like a charm at first try !
thanks man!
you saved me lot of time
Hey!
Thanks…. Works perfect… 2008 R2
Yep! Good advice!
Thank you for the guide. It worked perfectly!
Thanks – that solved my problem and I appreciated how easily you explained he process
Hi Simon,
Thank you very much for your feedback. 🙂
Best regards,
Paul
Thanks. Worked for me.
Awesome step-by-step article! I tried this on SQL Server 2012 and it worked and I can now edit maintenance plans. Thanks!
Thank you, thank you, thank you!
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!
Thank you very much for your feedback, I really appreciate.
Best regards,
Paul
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.
AWESOME – thanks so much!
Nice walk thru, helped heaps. Thanks.
A+++ poster 😉
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.
Thanks a lot! It worked.
Brilliant, thanks for this. It was very frustrating not being able to modify a Maintenance plan and not knowing why. This worked a treat.
Thanks .. It worked and was much appreciated !!
Hey Paul, Mike here, your old collegue of LogicaCMG? Great article! Helped me out here! Thanks. Cheers, Mike
Thanks, that did help! Regards, Jens
Thank you very much!
Thank you. it was exactly what I needed and succeeded to run maintenance plan. You are a genius!
I was on the verge of tears before I found your article. Thank you ever so much for taking the time to post this.
Thanks for the detailed steps, exactly what I was looking for and worked a treat.
Thanks, solved the problem here, much appreciated!
Very helpful ! Many thanks …
Thank you!!
How will this affect data? Will it be picked up by the new installation?
Hello Jake,
Data is not affected by this action.
Best regards,
Paul
Excellent!
Fixed my boat! Thank you!
Thanks to you very much for this
thank you, fixed.
Flawless technical write-up. Thank you, Sir!
Bless you.
great work, thx a lot !!
It worked for me too :-)\
I was affraid to run remove utility, but everything was OK. Thanks again
Thanks for this blog post. I found that the removal step was not necessary and I skipped to the installation step without issue.
Thank you for this useful feedback!