MS SQL 2005 Upgrade problem - looks like msdb didn't upgrade - how fix?

Question

Upgrading a production MS SQL Server from 2000 to 2005, the upgrade froze for a couple of hours. The process was killed and everything works except that we have no access to maintenance plans. When accessing them, we get:

TITLE: Microsoft SQL Server Management Studio ------------------------------ Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Invalid object name 'msdb.dbo.sysmaintplan_plans'. (Microsoft SQL Server, Error: 208) For help, click: http://go.microsoft.com/fwlink? ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ 

Comparing the msdb on the problem machine with other working 2005 installs, it looks like the msdb db did not get upgraded properly.

Before I take the server down, detach and rebuild the msdb per instructions I found here, http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx, I wanted to see if anyone else had suggestions of what to look for or how to fix the msdb without downtime (this is a production system).

Thanks.

Answer 1

As you interrupted the upgrade process god only knows what other parts didn't get completely upgraded. Have you reviewed the logs to see why the upgrade hung?

At this point I'd recommended that you burn the server and rebuild it from scratch. It's the only way to ensure that you don't run into strange problems later.