I want to move all indexes to a separate hard drive. To that effect, I created a new filegroup, then added a new file to it (e.g.
alter database foo add file(...) to FileGroup).
Now I want to move all the indexes to this new filegroup. Turns out, I can't use
ALTER INDEX to move an index between filegroups. And I can't move PK or Unique indexes either, lest I want to move the table itself (which I don't), so those are out. So, at least, I'd like to move the rest of the indexes.
I am struggling to find a method to move all eligible indexes from one filegroup to another. Is there a reasonable automated way to do this?