Manually Remove Datastores from VirtualCenter 2.0.x Database

Removing invalid datastores from the VirtualCenter database (in 2.0.x at least) is a bit of a pain... y'know, the ones that don't actually surface in the UI but you know are there because you get a "duplicate name" style error when adding a new datastore.

Manually editing the VirtualCenter database should never be taken lightly, and if there's a stored procedure or something that actually does this then someone please let me know. Otherwise, do the following:

1) Stop the VirtualCenter service and BACKUP THE VIRTUALCENTER DATABASE
2) Fire up whatever database management tool you like and connect to the VirtualCenter database.
3) Fire off the following query:

select * from VPX_DATASTORE

4) Find the offending datastore in the returned results, and make a note of the ID number (the first column)
5) Fire off another query:

select * from VPX_DS_ASSIGNMENT where DS_ID = id of datatstore to delete from previous query

6) Delete all rows returned from this query - all the values should be NULL (except the DS_ID column of course)
7) Go back to the VPX_DATASTORE table and delete the row with the non-existent datastore in it

In the case where you have a number of hosts that show as "inaccessible" as a result, you could put together a few more queries to handle that too... or even better do it in PowerShell! Hmmm, will have a look at that a bit later.