I have a very large database with about 20 filegroups. We want to consolidate
down to 3 or 4 file groups. My question is how can we do that in TSQL? I am
trying to do it during a restore but have not found a good solution. I can
create the filegroups and new files but have not found a way to attach the
existing files to the new filegroups.

You'll need to move objects to the filegroups you want to retain. You can
accomplish this in SQL Server 2005 with ALTER INDEX, ALTER TABLE or CREATE
INDEX...WITH DROP EXISTING. Delete the empty files/filegroups afterward.

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Jim Reed" <JimReed@discussions.microsoft.com> wrote in message
news:2717BB51-FC86-4635-817B-344E999F2BE2@microsoft.com...
>I have a very large database with about 20 filegroups. We want to
>consolidate
> down to 3 or 4 file groups. My question is how can we do that in TSQL? I
> am
> trying to do it during a restore but have not found a good solution. I can
> create the filegroups and new files but have not found a way to attach the
> existing files to the new filegroups.

Hi,

As Dan suggests, you are going to have to do this the manually.

May I recommend that before you go ahead and make the changes, that you
restore a copy of your production database to a test server. You can then
create and test scripts required in order to conduct your migration. Often in
order to move tables you will need to drop constraints beforehand, and then
re-apply them afterward so your migration is going to require some careful
planning.

One thing to watch out for is if you have tables with BLOB data such as the
image data type, moving the clustered index, will not work as the data is
stored separately from the table and index data itself. In order to move
these types of tables you will need to create a new table in the target
filegroup and populate it manually. If you script out the create statements
for tables of this type you will see that the binary data has it’s own
Filegroup assignment statement and hence the data is stored independently of
the table data.

Hope this makes sense, good luck with your project and be sure to let me
know if I can be of any further assistance.

Remember, careful planning is the key to a successful project.

Best Regards,
John Sansom


"Dan Guzman" wrote:

> You'll need to move objects to the filegroups you want to retain. You can
> accomplish this in SQL Server 2005 with ALTER INDEX, ALTER TABLE or CREATE
> INDEX...WITH DROP EXISTING. Delete the empty files/filegroups afterward.
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Jim Reed" <JimReed@discussions.microsoft.com> wrote in message
> news:2717BB51-FC86-4635-817B-344E999F2BE2@microsoft.com...
> >I have a very large database with about 20 filegroups. We want to
> >consolidate
> > down to 3 or 4 file groups. My question is how can we do that in TSQL? I
> > am
> > trying to do it during a restore but have not found a good solution. I can
> > create the filegroups and new files but have not found a way to attach the
> > existing files to the new filegroups.
>