August 8, 2013 at 7:22 am
Books Online isn't very clear on this subject and Google-Fu suggests that what I want to do should be possible, but I can't find any examples to assist me, so I'm hoping people here can help.
I want to move a file in a Secondary file group back to the Primary file group. This is cleanup. The database in question is getting upgraded and moved to a better server with a single drive, so we don't need multiple files & file groups in multiple locations.
I know it's an ALTER DATABASE statement, but MODIFY FILE only allows me to modify the name and file size, not the file group it's hiding in (that I can see). And trying DBCC SHRINKFILE with EMPTYFILE fails because there are no other files in the Secondary file group that the data can be moved to.
Can someone point me to a link or give be a better search term than "moving data between filegroups"?
August 8, 2013 at 9:53 am
You can't move a file from filegroup to a filegroup. But if I understood you correctly, your ultimate goal is not moving the files around, but to get rid of unnecessary secondary filegroup.
The reason why you cannot emptyfile is not because you don't have another file in same FG, but because some tables or indexes are sitting there. You can easily check it with sys.indexes and sys.filegroups. Recreate them in Primary With drop_existing and by specifying ...ON [Primary]. In case if you have heaps, create a clustered index on them with, again, ON primary. You can drop them afterwards. After all these manipulations, you will be able to easily shrink the file with "emptyfile", and remove the file, as well as secondary filegroup.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply