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.