Moving indexes from a filegroup to another

9 Feb 2006 7:02 PM

florent.jeannot

Hi,

I have a 65 Gb database stored on a single filegroup and I would like
to move non-clustered indexes to another filegroup.

I'm thinking of the following process:

   _ Backup initial database
   _ Create new empty database with two filegroups (one for data, one
for indexes) (the database is created by a software that defines all
the objects on a single filegroup)

   _ Moving indexes (definition) from a filegroup to another while the
database is empty.
   _ Restore data and indexes from the previous backup so that data go
the data filegroup and indexes go to the indexes filegroup.

Another solution would be to drop the indexes and recreate them on the
new filegroup but moving 65 Gb will probably take a very long time.

Any ideas ?

Thanks !

9 Feb 2006 7:43 PM

Aaron Bertrand [SQL Server MVP]

> I have a 65 Gb database stored on a single filegroup and I would like
> to move non-clustered indexes to another filegroup.

The only index you can move is the clustered index (which effectively takes
the table and all the non-clustered indexes with it). You can do this by
dropping the clustered index and re-creating it on the new filegroup, by
using ON <filegroup>.

9 Feb 2006 9:21 PM

Andrew J. Kelly

If you want to separate the data (Heaps & Clustered indexes) from the
non-clustered indexes you will need to drop the non-clustered indexes and
recreate them on the new filegroup. YOu won't move all 65GB since the table
itself should be the majority of the data. There is no need to restore
anything.

--
Andrew J. Kelly SQL MVP

<florent.jean***@gmail.com> wrote in message

Show quoteHide quote

news:1139511741.514031.76310@o13g2000cwo.googlegroups.com...
> Hi,
>
> I have a 65 Gb database stored on a single filegroup and I would like
> to move non-clustered indexes to another filegroup.
>
> I'm thinking of the following process:
>
>   _ Backup initial database
>   _ Create new empty database with two filegroups (one for data, one
> for indexes) (the database is created by a software that defines all
> the objects on a single filegroup)
>
>   _ Moving indexes (definition) from a filegroup to another while the
> database is empty.
>   _ Restore data and indexes from the previous backup so that data go
> the data filegroup and indexes go to the indexes filegroup.
>
> Another solution would be to drop the indexes and recreate them on the
> new filegroup but moving 65 Gb will probably take a very long time.
>
> Any ideas ?
>
> Thanks !
>

11 Feb 2006 5:11 PM

Florent

Thanks Andrew. That was my first thought, but actually I was wondering
whether there would be a faster way to do it.