How to move all non-unique and non-clustered indexes to a different filegroup?

Question

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?

Answer 1

Create a new filegroup and file, rebuild the index on your filegroup.

In this case my index is actually on the filegroup PRIMARY, and i moved it to the new filegroup [TEST]. Drop and recreate the index, for recreating it is important that you give sql server the information where to store the new index. In my case [TEST].

USE [ShrinkIsBed]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FirstTable]') AND name = N'NX_TEST')
DROP INDEX [NX_TEST] ON [dbo].[FirstTable] WITH ( ONLINE = OFF )
GO

USE [ShrinkIsBed]
GO

CREATE NONCLUSTERED INDEX [NX_TEST] ON [dbo].[FirstTable] 
(
    [FirstName] ASC,
    [LastName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) **ON [TEST]**
GO

Answer 2

Found the answer. See comments on how to make it work for SQL Server 2005.