moving tables

[helen_msql]

Hi,

ms-sqlserver 2000 sp4 - entrerprise edition

Can you help me on this issue. I would like to move some user
tables(about 100 MB) to new file group. Can you tell me what I need to
do?

Thanks in advance

[Jack Vamvas]

1)backup db
2)generate the scripts for the tables
3)recreate the scripts in the other db
4)transfer data ,,by either DTS or export/import -depending on whether you
can connect to other filegroup

--
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"helen_msql" <el**************@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Hi,

ms-sqlserver 2000 sp4 - entrerprise edition

Can you help me on this issue. I would like to move some user
tables(about 100 MB) to new file group. Can you tell me what I need to
do?

Thanks in advance

[Erland Sommarskog]

helen_msql (el**************@gmail.com) writes:

ms-sqlserver 2000 sp4 - entrerprise edition

Can you help me on this issue. I would like to move some user
tables(about 100 MB) to new file group. Can you tell me what I need to
do?

If the table has a clustered index rerun the CREATE INDEX statement
with WITH DROP_EXISTING ON NewFileGroup.

If the table does not have a clustered index, create one that uses
the ON clause, and then drop the index.

As the data is the at leaf level of the clustered index, this will move
the data as well as the index. If there are non-clustered indexes on the
table, then you can move these in the same way as well.

Disclaimer: I have never actually done myself, as I've never worked
with multiple filegroups.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx