November 15, 2002 at 4:31 pm
Hello everyone, what is the best way to move a table (with all data, permissions, constraints, etc.) from one filegroup to another? Thank you.
November 15, 2002 at 4:49 pm
Do it in Enterprise manager and generate the script.
Basically you have to rename the table. recreate the table in the new filegroup and then copy the data.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 16, 2002 at 2:30 pm
Thank you. However, I have a dilema.
I have some tables with clustered index and they are created on my index filegroup. I want to move them to my primary filegroup. I was thinking that I would go to EM and remove the clustered index, change the filegroup, save the changes and then re-create the clustered index.
I tested this scenario on the development computer. Then I tested it on production and I got two different results.
On the development box, when I check the "Create as CLUSTERED" option, the filegroup changes from index to primary, which is what I want. In other words, when you want to create a clustered index, you can only create it on the primary filegroup and EM won't let you create it on the index filegroup.
On the production box, I removed the clustered index, changed the filegroup to primary, and saved the changes. I went back and checked the "Create as CLUSTERED" checkbox and the filegroup changes from primary to index. It won't let me create a clustered index table on the primary filegroup.
These two boxes are acting opposite from each other. Any idea why this is happening?
November 18, 2002 at 2:37 am
Use your favorite SQL Query Analyzer and type:
drop index TableName.IndexName
Where IndexName is your clustered index
And the type:
create clustered index IndexName on TableName(column, column...) on NewFileGroup
This one has effect to place your table on the new filegroup because the data will "follow" the clustered index
Bye
Gabor
November 18, 2002 at 6:06 am
The clustered index has to exist in the same filegroup as the data, because the base of the index is the data.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
June 20, 2005 at 7:45 am
You can find a script that would move the data, indexes, and more to a new filegroup on
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply