June 21, 2011 at 7:04 am
Hi All
I have 2 FileGroups in a database in SQLServer, and I would like to move a file within FileGroup A to FileGroup B.
Is this possible without using DBCC ShrinkFile and then creating a new file in the destination Group?
If so, how? I've searched Google and came up with nothing so far, I would have thought it would be a straightforward Alter Database statement, but I can't find any examples.
June 21, 2011 at 12:38 pm
Files cannot be moved between filegroups. End of story.
You can use Shrinkfile with the EmptyFile option to empty the file so that it can be dropped, but that's it. If you want to move objects (tables/indexes) to a different filegroup, rebuild the clustered index (for table) or index on the new filegroup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2011 at 3:22 am
Wonderful, thanks for your response.
June 22, 2011 at 5:25 am
GilaMonster (6/21/2011)
Files cannot be moved between filegroups. End of story.You can use Shrinkfile with the EmptyFile option to empty the file so that it can be dropped, but that's it. If you want to move objects (tables/indexes) to a different filegroup, rebuild the clustered index (for table) or index on the new filegroup.
Thank you Gail, very useful
M&M
June 24, 2011 at 1:49 am
I was checking below link related to moving tables from one filegroup to another.
http://stackoverflow.com/questions/2438019/how-i-can-move-table-to-another-filegroup
The explanation is fine. However, at the end of the link, there is below statement.
Yep. And we're both wrong if the PK constraint is clustered.
I am not sure what this means.Can any one please clarify.
M&M
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply