Moving tables from one filegroup to other

Hi
In sql2000, what are ways to move tables from one Filegroup to other.
and what is the best way to move the tables with least of impact on system
and least of downtime.
Thanks
Mangesh
You can move a table by re-creating the clustered index on the other
filegroup using CREATE INDEX ... WITH DROP_EXISTING and specifying the new
filegroup in the ON clause. This will probably be the fastest way to move
the table, but amount of downtime will depend on how big the table is and
how fast your disks are! It may be quite slow on a large table...
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:074875FA-F5EB-4312-91DD-EBB06FBE487C@.microsoft.com...
> Hi
> In sql2000, what are ways to move tables from one Filegroup to other.
> and what is the best way to move the tables with least of impact on system
> and least of downtime.
> Thanks
> Mangesh
|||Moving the clustered index using "create index ... with DROP_EXISTING on
filegroup".
AMB
"Mangesh Deshpande" wrote:

> Hi
> In sql2000, what are ways to move tables from one Filegroup to other.
> and what is the best way to move the tables with least of impact on system
> and least of downtime.
> Thanks
> Mangesh
|||A fairly simple way is to right click on the table and select design table.
In the table area of the pane that appears right click and select properties.
There should be a drop down that says "Table Filegroup". Use the drop-down
and select another filegroup. Re-save the table and you're done.
"Mangesh Deshpande" wrote:

> Hi
> In sql2000, what are ways to move tables from one Filegroup to other.
> and what is the best way to move the tables with least of impact on system
> and least of downtime.
> Thanks
> Mangesh
|||Cathy,
It is simple indeed, but not optimal. Try adding a filegroup to northwind db
and adding a file to this new filegroup, then go and modify table [orders] to
move it to this new filegroup. Before saving the changes, click "save change
script" button (third from left to right in the toolbar) and you will see the
script that EM will use to move the table.
AMB
"Cathy Soloway" wrote:
[vbcol=seagreen]
> A fairly simple way is to right click on the table and select design table.
> In the table area of the pane that appears right click and select properties.
> There should be a drop down that says "Table Filegroup". Use the drop-down
> and select another filegroup. Re-save the table and you're done.
> "Mangesh Deshpande" wrote:
|||Thanks.
On similar lines is it possible to move the datafile from one filegroup to
other file
group.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Cathy,
> It is simple indeed, but not optimal. Try adding a filegroup to northwind db
> and adding a file to this new filegroup, then go and modify table [orders] to
> move it to this new filegroup. Before saving the changes, click "save change
> script" button (third from left to right in the toolbar) and you will see the
> script that EM will use to move the table.
>
> AMB
> "Cathy Soloway" wrote:
|||No. The data file potentially already have pages allocated to the objects belonging to the original
filegroup. There's no command to move those pages to other files so file becomes empty and
re-allocate the file to another file group. You need to do this manually. First shrink using
EMPTYFILE option. Then remove the file (ALTER DATABASE). Then add another file to the other
filegroup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
news:57C71DB8-6291-44BE-A900-E2057DACEF07@.microsoft.com...[vbcol=seagreen]
> Thanks.
> On similar lines is it possible to move the datafile from one filegroup to
> other file
> group.
>
> "Alejandro Mesa" wrote:
|||I wrote a thorough script that allow you to move table to a new filegroup.
The script user can specify whether only data is moved, or also other indexes
and constraints.
It's a fairly long script, but a very robust one. You can find it on
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com
sql