Migrate database indexes to a new file group

I recently had to mass-migrate all the indexes from a database to a new file group since we’d added some additional storage to our database server. I found this article at SQL Server Central (unfortunately, registration required, so I’ve included a copy of the original script in the download at the end). While it worked okay, there were some things I didn’t like about it:

  • Assumed 90% index fill rate
  • “Moved” indexes were all created as non-unique, regardless of original
  • Fail during index creation left you without an index (drop and then create, with no rollback)
  • Table was un-indexed during the move (index dropped and then created)
  • Script re-created indexes without any “Included” columns, even if original index had them

To address these limitations, I rebuilt the process using that script as a starting point. The new script:

  • Uses 90% fill rate by default, but if the original index had a different rate specified, it will use that
  • Re-creates indexes as unique if the source index was unique
  • Rollback problem resolved – new index is created with different name, old index is dropped, and then new index is renamed, all in a TRY-CATCH block
  • Since the new index is created and then the old one dropped, table indexing remains “online” during the move
  • Migrates “Included” columns in index
  • Updated the script to use SYS views (breaks compatibility with SQL 2000, since SYS is 2005/2008/beyond only)
I welcome any feedback on the script, and would love to know if you see any improvements that should be made.

Download .SQL scripts (contains both Original and Modified scripts)

2 thoughts on “Migrate database indexes to a new file group”

  1. Hello,

    This is a terribly old post, but I still find it useful. Just one thing I thought of mentioning is that the script has a filter for heap tables alone(p.index_id = 0). the sys.partitions table has an entry for heap, clustered and nonclustered indexes on a table. That is a counters the filter on sys.indexes table (si.index_id between 2 and 254 – which means all the indexes apart from the clustered) Once I get rid fo the filter on sys.partitions table, the script works well.

    Thanks much and appreciate the effort!


    1. I’m happy to hear that it’s helpful! I had originally added the filter to make sure I was moving the indexes and not the data itself. It can definitely be removed if you want to move clustered indexes.


Leave a Reply

Your email address will not be published. Required fields are marked *

Why ask?