R2 Upgrade Fails when Dropping Indexes that Don't Exist

Vote:
 

I'm trying to upgrade a v6R1 site to R2.  Everything goes fine right up until the database changes, and then it fails trying to delete an index because I don't have permissions or the index doesn't exist.

I went and checked, and it's right -- the index doesn't exist.  So I commented out that line in the SQL...and then it fails on a new DROP INDEX statement.  I did this for 15 of the 33 DROP INDEX statements.

Finally, just for fun, I commented out all the DROP INDEX lines...and then it fails trying to do something with an index that didn't get dropped.

So, I have some indexes, but not others, for some reason.

How do I get around this?  I now have a site which is out of sync with my database.

Deane

#56383
Jan 19, 2012 21:53
Vote:
 

Hi Deane,

Don't know if this will solve it as your database schema seems corrupt or something, but you could try to drop all the indexes in the database and then let the script run (with drop index commented out).

#56384
Jan 20, 2012 9:34
Vote:
 

I've thought about this, but this plan assumes the upgrade script recreates all indexes in the end (the indexes that it would have dropped).  Does it?

And I agree that something seems very corrupt about this situation, but -- for the record -- the installation has been functioning just fine.  If the missing indexes affected performance, no one noticed it.

#56385
Jan 20, 2012 9:44
Vote:
 

You can always take a backup of the database, run a script that lists all the index, compare that after the upgrade and see if there is any indexes missing.

Here's one of those scripts (sql 2005):

http://snippets.dzone.com/posts/show/4207 

#56386
Jan 20, 2012 9:46