SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by michael vessey / move all indexes to a secondary file group / Latest Posts

RE: move all indexes to a secondary file group

Ryan McCauley — Wed, 02 Jan 2013 09:09:40 GMT

To do this, you have to create a clustered index on the table and specify the new file group for the index - once you've moved it, you can drop the index if you don't need it. Effectively, you'd use this same script, but you'd remove the filter that causes it to ignore clustered indexes so that it migrates those as well.

RE: move all indexes to a secondary file group

Arju — Wed, 02 Jan 2013 07:26:10 GMT

Hi,Can some one provide links to move tables & indexes to other file group without drop & Recreate them.Just like to move object to other file group without drop and recreate.Thanks

RE: move all indexes to a secondary file group

Ryan McCauley — Wed, 28 Sep 2011 16:21:13 GMT

I've fixed the redirection on my blog, so you can view it now. I must have changed the layout at some point and not set up this forwarding page properly.Sorry for the confusion.

RE: move all indexes to a secondary file group

AK1516 — Wed, 28 Sep 2011 15:17:28 GMT

is this link working ?????????

RE: move all indexes to a secondary file group

Ryan McCauley — Mon, 08 Feb 2010 10:44:30 GMT

I've updated this script to do a few additional things, like- Properly re-create unique indexes- Handle Included columns- Do "online" index moves w/error handling - creates new index, drops old, then renames- Uses existing fill factor if the source index explicitly defines itI've posted the script at my blog - I'm interested if anybody else has feedback or something they'd like to see added.[url=http://www.trycatchfinally.net/post/2010/02/08/Migrate-database-indexes-to-a-new-file-group.aspx]http://www.trycatchfinally.net/post/2010/02/08/Migrate-database-indexes-to-a-new-file-group.aspx[/url]

RE: move all indexes to a secondary file group

repent_kog_is_near — Mon, 05 Oct 2009 21:21:22 GMT

What is the logic behind keeping Non-Clustered with Primary and Clustered with Secondary?

RE: move all indexes to a secondary file group

SQL2005-982522 — Wed, 07 Jan 2009 11:03:02 GMT

Got an error about foreign key. Any idea?Msg 3723, Level 16, State 6, Line 1An explicit DROP INDEX is not allowed on index 'xxxxxxxxxxxxxx'. It is being used for FOREIGN KEY constraint enforcement.

RE: move all indexes to a secondary file group

hydbadrose — Fri, 10 Oct 2008 14:59:26 GMT

I loved the script. I wish the author or some cold post the reverse process like moving non-clustered indexes from secondary to primary with clustered indexes.

RE: move all indexes to a secondary file group

timothyawiseman — Sun, 31 Aug 2008 09:47:08 GMT

[quote][b]keppro (8/30/2008)[/b][hr]Thanks a lot to the author!One problem with the script: it doesn't recognize indexes with included fields. Does anybody know what system table(s) contain this information? Better yet, is there any kind of API for Management Studio's scripting?[/quote]It is in sys.index_columns, with the included columns marked by a 1 in the is_included column.

RE: move all indexes to a secondary file group

keppro — Sat, 30 Aug 2008 17:15:02 GMT

Thanks a lot to the author!One problem with the script: it doesn't recognize indexes with included fields. Does anybody know what system table(s) contain this information? Better yet, is there any kind of API for Management Studio's scripting?

RE: move all indexes to a secondary file group

timothyawiseman — Mon, 18 Aug 2008 17:43:59 GMT

I really like the script, but it does not maintain the unique portion of indexes which makes it hard to use in some cases.

move all indexes to a secondary file group

MVDBA — Sat, 29 Sep 2007 21:25:43 GMT

Comments posted to this topic are about the item [B]move all indexes to a secondary file group[/B]