Split existing database into two filegroups

[sab4you]

I have a SQL database in a single filegroup already created with tables and filled with data.

I want to split this into two different filegroups, so I can put the indexes onto one filegroup and the rest into the second.

I am able to create a second filegroup, but I lack the knowledge to now move existing tables into this second filegroup.

Can anybody lead me in the right way?

 

[Corran007]

out of books online

for tables:

Expand a server group, and then expand a server.

Expand Databases, expand the database in which the table belongs, and then click Tables.

In the details pane, right-click the table, and then click Design Table.

Right-click any column, and then click Properties.

On the Tables tab, in the Table Filegroup list, select the filegroup on which to place the table.

Optionally, in the Text Filegroup list, select a filegroup on which to place any text, image, and ntext columns.


For indexs:
Expand a server group, and then expand a server.

Expand Databases, expand the database in which the table containing the index belongs, and then click Tables.

In the details pane, right-click the table, and then click Design Table.

Right-click any column, and then click Properties.

On the Indexes/Keys tab, in the Selected index list, select the index to move.

In the Index Filegroup list, select a filegroup on which to place the index.


I included the one for index because you do not have to switch the tables if you just want to change the indexs. For indexs, it may be faster to drop and recreate them if you have a great deal of them.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]

< index_option > :: =
{ PAD_INDEX |
FILLFACTOR = fillfactor |
IGNORE_DUP_KEY |
DROP_EXISTING |
STATISTICS_NORECOMPUTE |
SORT_IN_TEMPDB
}

Just script out the index and change the file group.

 

[sab4you]

Thanks for the tips, that got me going in the right direction.

Now here is what I have:
1. Some of my tables have clustered indexes which I cannot move to my secondary file group. I am guessing clustered indexes need to be in the primary file group?

2. Lets say I have an existing database with all kinds of data in the tables. If I move my indexes to a new file group - is it just going to add the new stuff to this file group, or how can I get it to move the existing data over into the new group?

 

[Corran007]

1. makes since since clustered physcially aranage the data. if you moved the table, the index would move for clustered ones.

2. Data is stored in the tables. Moving over the idexes will not affect the data at all. If your talking data, then yoru talking tables and you need to change the filegroup for the table.

If ON is specified when adding a clustered index for a PRIMARY KEY or UNIQUE constraint, the entire table is moved to the specified filegroup when the clustered index is created.

It might be worth trying to drop an exsiting cluster/unique constraint and see if when it creates it if it really moves it. Do it on a small table first since it unrearanges the data after you remove the cluster, then will reapply it.

hope this helps