Moving to a filegroup

I need to move some tables to a new filegroup
I'm actually working only in the primary group
Which is the best way to do this?
Thanks
RicardoRicardo
If you have clustered index defined on your table, so you can by CREATE
CLUSTERED INDEX ON (Filegroup) to place the table on another filegroup
For more deatils please refer to the BOL.
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
create table t1(col1 int) on new_customers
create table t2(col1 int) on sales
"Ricardo" <anonymous@.discussions.microsoft.com> wrote in message
news:441D13F7-DF9C-4E8C-8D82-9DDDA0D2813A@.microsoft.com...
> I need to move some tables to a new filegroup
> I'm actually working only in the primary group
> Which is the best way to do this?
> Thanks
> Ricardo|||Hi Ricardo.
Your would be able to Create to new Filegroup and to Create a New DataFile a
nd to establish this the new FileGroup. Then you should modify the tables t
hat you want to move the new filegroup.
You can utilize the Query Analyzer and to execute the "Alter Table" statemen
t for each table.
Hermilson.
****************************************
********************
I need to move some tables to a new filegroup
I'm actually working only in the primary group
Which is the best way to do this?
Thanks
Ricardo

Moving to a filegroup

I need to move some tables to a new filegrou
I'm actually working only in the primary grou
Which is the best way to do this
Thank
RicardoRicardo
If you have clustered index defined on your table, so you can by CREATE
CLUSTERED INDEX ON (Filegroup) to place the table on another filegroup
For more deatils please refer to the BOL.
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
create table t1(col1 int) on new_customers
create table t2(col1 int) on sales
"Ricardo" <anonymous@.discussions.microsoft.com> wrote in message
news:441D13F7-DF9C-4E8C-8D82-9DDDA0D2813A@.microsoft.com...
> I need to move some tables to a new filegroup
> I'm actually working only in the primary group
> Which is the best way to do this?
> Thanks
> Ricardo|||Hi Ricardo
Your would be able to Create to new Filegroup and to Create a New DataFile and to establish this the new FileGroup. Then you should modify the tables that you want to move the new filegroup
You can utilize the Query Analyzer and to execute the "Alter Table" statement for each table
Hermilson
***********************************************************
I need to move some tables to a new filegrou
I'm actually working only in the primary grou
Which is the best way to do this
Thank
Ricard

Moving the index to different filegroup

There is already a database installed in my site and spilted on different fi
legroups. I want to know which object is located on which filegroup. prefera
bly the indexes and if I want to move them to another filegroup how can I do
.
Thanks in advance
Regards,
sunilHi,
Best approach is to drop and create the index in the new file group using
Create index command.
Please have a look into the below link for more information,
http://www.sqljunkies.com/HowTo/B9F...43A8681900.scuk
Thanks
Hari
MCDBA
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:7BA0A331-3990-4330-8837-9C871B24A9F4@.microsoft.com...

quote:

---

> There is already a database installed in my site and spilted on different

---

filegroups. I want to know which object is located on which filegroup.
preferably the indexes and if I want to move them to another filegroup how
can I do.

quote:

---

>
> Thanks in advance
> Regards,
> sunil

---

|||select
object_name(i.id) as table_name,
i.name as index_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.groupid = s.groupid
and i.name not like '_WA_Sys_%'
order by filegroup, table_name, index_name
Sunil wrote:

quote:

---

> There is already a database installed in my site and spilted on different
filegroups. I want to know which object is located on which filegroup. prefe
rably the indexes and if I want to move them to another filegroup how can I
do.
> Thanks in advance
> Regards,
> sunil

---

Moving the index to different filegroup

There is already a database installed in my site and spilted on different filegroups. I want to know which object is located on which filegroup. preferably the indexes and if I want to move them to another filegroup how can I do
Thanks in advanc
Regards
sunilHi,
Best approach is to drop and create the index in the new file group using
Create index command.
Please have a look into the below link for more information,
http://www.sqljunkies.com/HowTo/B9F7F302-964A-4825-9246-6143A8681900.scuk
Thanks
Hari
MCDBA
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:7BA0A331-3990-4330-8837-9C871B24A9F4@.microsoft.com...
> There is already a database installed in my site and spilted on different
filegroups. I want to know which object is located on which filegroup.
preferably the indexes and if I want to move them to another filegroup how
can I do.
>
> Thanks in advance
> Regards,
> sunil|||select
object_name(i.id) as table_name,
i.name as index_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.groupid = s.groupid
and i.name not like '_WA_Sys_%'
order by filegroup, table_name, index_name
Sunil wrote:
> There is already a database installed in my site and spilted on different filegroups. I want to know which object is located on which filegroup. preferably the indexes and if I want to move them to another filegroup how can I do.
> Thanks in advance
> Regards,
> sunil

Moving the index

Hello
Objective: move nonclustered index from one filegroup to another
Complication: index is large (>4Gb) and system can't be stopped
during this movement operation (CREATE INDEX WITH DROP
EXISTING will lock entire table). Allowed downtime - 1-2 mins.
Solution: unknown.
Serge ShakhovI don't think it can be done...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Serge Shakhov" <ACETYLENE@.mail.ru> wrote in message
news:67hhjb.bm9.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> Objective: move nonclustered index from one filegroup to another
> Complication: index is large (>4Gb) and system can't be stopped
> during this movement operation (CREATE INDEX WITH DROP
> EXISTING will lock entire table). Allowed downtime - 1-2 mins.
> Solution: unknown.
> Serge Shakhov
>
>

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

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
MangeshYou 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 [order
s] 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 th
e
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 properti
es.
> There should be a drop down that says "Table Filegroup". Use the drop-do
wn
> 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 [ord
ers] to
> move it to this new filegroup. Before saving the changes, click "save chan
ge
> 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 be
longing to the original
filegroup. There's no command to move those pages to other files so file bec
omes empty and
re-allocate the file to another file group. You need to do this manually. Fi
rst shrink using
EMPTYFILE option. Then remove the file (ALTER DATABASE). Then add another fi
le 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 me
ssage
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 indexe
s
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

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
MangeshYou 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:
> 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|||Thanks.
On similar lines is it possible to move the datafile from one filegroup to
other file
group.
"Alejandro Mesa" wrote:
> 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:
> > 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|||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...
> Thanks.
> On similar lines is it possible to move the datafile from one filegroup to
> other file
> group.
>
> "Alejandro Mesa" wrote:
>> 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:
>> > 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|||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

moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI on PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/B9F7F302-964A-4825-9246-6143A8681900.scuk
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||Thanks all.
I have one more question. How do I move the log file from the default location
to another one.
I would like to add a new file to the log file and remove the first file to the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
> own.
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option in TSQL
to add a column at the desired location. The newly added NTEXT column should be
right before the existing NTEXT column, so that after dropping the existing NTEXT
column, its position is taken up by this new column. This is the only way to guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series of steps
it does to mimic that.|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
--
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI on PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.
Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>
|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/B...3A8681900.scuk
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>
|||Thanks all.
I have one more question. How do I move the log file from the default location
to another one.
I would like to add a new file to the log file and remove the first file to the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.
|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
> own.
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option in TSQL
to add a column at the desired location. The newly added NTEXT column should be
right before the existing NTEXT column, so that after dropping the existing NTEXT
column, its position is taken up by this new column. This is the only way to guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series of steps
it does to mimic that.
|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com
sql

moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI o
n PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns
and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate fil
egroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/...43A8681900.scuk
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||Thanks all.
I have one more question. How do I move the log file from the default locati
on
to another one.
I would like to add a new file to the log file and remove the first file to
the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its[
vbcol=seagreen]
> own.[/vbcol]
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be
used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option i
n TSQL
to add a column at the desired location. The newly added NTEXT column should
be
right before the existing NTEXT column, so that after dropping the existing
NTEXT
column, its position is taken up by this new column. This is the only way to
guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series
of steps
it does to mimic that.|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

Moving Tables

I'm aware of the two methods of moving a table from one filegroup to another
:
(re)create a clustered index on the new filegroup or create the table on the
new filegroup then bcp data into new table.
Is there just a simple way to say "move from PRIMARY to whatever"?
Thanks!!Hi,
Easiest method is to re-create the clustered index in new file group. This
will move the table to the new file group automatically.
Enterprise Manager moves a table from one filegroup to another without using
an undocumented task, but T-SQL doesn't have a command that does the same
thing. The easiest way to move a table to another filegroup is to create a
clustered index on the table. If the table already has a clustered index,
you can use the CREATE INDEX command's WITH DROP_EXISTING clause to recreate
the clustered index and move it to a particular filegroup. When a table has
a clustered index, the leaf level of the index and the data pages of the
table essentially become one and the same. The table must exist where the
clustered index exists, so if you create or recreate a clustered
index-placing the index on a particular filegroup-you're moving the table to
the new filegroup as well.
Thanks
Hari
SQL Server MVP
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:D6A7F27B-309D-40C1-9663-D28B2514673A@.microsoft.com...
> I'm aware of the two methods of moving a table from one filegroup to
> another:
> (re)create a clustered index on the new filegroup or create the table on
> the
> new filegroup then bcp data into new table.
> Is there just a simple way to say "move from PRIMARY to whatever"?
> Thanks!!
>|||I was kinda afraid of that...that's the way I've done it in the past.
Thanks!
What aboutr setting a filgroup to read only - through the gui only'
"Hari Pra" wrote:

> Hi,
> Easiest method is to re-create the clustered index in new file group. This
> will move the table to the new file group automatically.
> Enterprise Manager moves a table from one filegroup to another without usi
ng
> an undocumented task, but T-SQL doesn't have a command that does the same
> thing. The easiest way to move a table to another filegroup is to create a
> clustered index on the table. If the table already has a clustered index,
> you can use the CREATE INDEX command's WITH DROP_EXISTING clause to recrea
te
> the clustered index and move it to a particular filegroup. When a table ha
s
> a clustered index, the leaf level of the index and the data pages of the
> table essentially become one and the same. The table must exist where the
> clustered index exists, so if you create or recreate a clustered
> index-placing the index on a particular filegroup-you're moving the table
to
> the new filegroup as well.
> Thanks
> Hari
> SQL Server MVP
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:D6A7F27B-309D-40C1-9663-D28B2514673A@.microsoft.com...
>
>|||Hi,
I have moved the table to different file group couple of times. But ensure
that you take a full database backup before doing this.
I have never tried setting a file group to read only from GUI, but i did
once using TSQL. But i belive to set the file group to read only first you
need to set the database to SINGLE USER Mode.
Thanks
Hari
SQL Server MVP
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:3613F67F-6AE7-4A5A-987D-85C5269675F1@.microsoft.com...
>I was kinda afraid of that...that's the way I've done it in the past.
> Thanks!
> What aboutr setting a filgroup to read only - through the gui only'
> "Hari Pra" wrote:
>

Moving table to different filegroup using t-sql

does anyone know how to move a table (not just the indexes) to a different filegroup? I only find info on how to do this in EM. Even when I run a trace while doing it in EM I don't realy see anything in the trace that performs the action?
I'm trying to schedule this for the nighttime - hence the reason I want to do it through t-sql.
DBA72,
Do you have a clustered index on your table? If you do, you can move the
table to the new filegroup by issuing a create index statement WITH
DROP_EXISTING clause.
i.e. something like:
CREATE CLUSTERED INDEX mynewfilegroup ON mytable (col1, coln)
WITH DROP_EXISTING
ON filegroupname
Otherwise you will have to do what EM does. Create a temp table move your
data there, then create a new table on the new filegroup and copy it over.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"DBA72" <anonymous@.discussions.microsoft.com> wrote in message
news:AEF7FE8D-FB1A-4A58-81F5-149F65357BDB@.microsoft.com...
> does anyone know how to move a table (not just the indexes) to a different
filegroup? I only find info on how to do this in EM. Even when I run a trace
while doing it in EM I don't realy see anything in the trace that performs
the action?
> I'm trying to schedule this for the nighttime - hence the reason I want to
do it through t-sql.
|||And if the table does not have a clustered index, you simply add one,
and then drop it again. If you drop the clustered index, the data will
stay on the same filegroup. I.e. something like:
CREATE CLUSTERED INDEX indextodrop ON mytable (col1, coln) ON
filegroupname
DROP INDEX mytable.indextodrop
Hope this helps,
Gert-Jan
Mark Allison wrote:
> DBA72,
> Do you have a clustered index on your table? If you do, you can move the
> table to the new filegroup by issuing a create index statement WITH
> DROP_EXISTING clause.
> i.e. something like:
> CREATE CLUSTERED INDEX mynewfilegroup ON mytable (col1, coln)
> WITH DROP_EXISTING
> ON filegroupname
> Otherwise you will have to do what EM does. Create a temp table move your
> data there, then create a new table on the new filegroup and copy it over.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "DBA72" <anonymous@.discussions.microsoft.com> wrote in message
> news:AEF7FE8D-FB1A-4A58-81F5-149F65357BDB@.microsoft.com...
> filegroup? I only find info on how to do this in EM. Even when I run a trace
> while doing it in EM I don't realy see anything in the trace that performs
> the action?
> do it through t-sql.
(Please reply only to the newsgroup)

Moving table to different filegroup using t-sql

does anyone know how to move a table (not just the indexes) to a different f
ilegroup? I only find info on how to do this in EM. Even when I run a trace
while doing it in EM I don't realy see anything in the trace that performs t
he action?
I'm trying to schedule this for the nighttime - hence the reason I want to d
o it through t-sql.DBA72,
Do you have a clustered index on your table? If you do, you can move the
table to the new filegroup by issuing a create index statement WITH
DROP_EXISTING clause.
i.e. something like:
CREATE CLUSTERED INDEX mynewfilegroup ON mytable (col1, coln)
WITH DROP_EXISTING
ON filegroupname
Otherwise you will have to do what EM does. Create a temp table move your
data there, then create a new table on the new filegroup and copy it over.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"DBA72" <anonymous@.discussions.microsoft.com> wrote in message
news:AEF7FE8D-FB1A-4A58-81F5-149F65357BDB@.microsoft.com...
> does anyone know how to move a table (not just the indexes) to a different
filegroup? I only find info on how to do this in EM. Even when I run a trace
while doing it in EM I don't realy see anything in the trace that performs
the action?
> I'm trying to schedule this for the nighttime - hence the reason I want to
do it through t-sql.|||And if the table does not have a clustered index, you simply add one,
and then drop it again. If you drop the clustered index, the data will
stay on the same filegroup. I.e. something like:
CREATE CLUSTERED INDEX indextodrop ON mytable (col1, coln) ON
filegroupname
DROP INDEX mytable.indextodrop
Hope this helps,
Gert-Jan
Mark Allison wrote:
> DBA72,
> Do you have a clustered index on your table? If you do, you can move the
> table to the new filegroup by issuing a create index statement WITH
> DROP_EXISTING clause.
> i.e. something like:
> CREATE CLUSTERED INDEX mynewfilegroup ON mytable (col1, coln)
> WITH DROP_EXISTING
> ON filegroupname
> Otherwise you will have to do what EM does. Create a temp table move your
> data there, then create a new table on the new filegroup and copy it over.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "DBA72" <anonymous@.discussions.microsoft.com> wrote in message
> news:AEF7FE8D-FB1A-4A58-81F5-149F65357BDB@.microsoft.com...
> filegroup? I only find info on how to do this in EM. Even when I run a tra
ce
> while doing it in EM I don't realy see anything in the trace that performs
> the action?
> do it through t-sql.
(Please reply only to the newsgroup)

Moving table to different filegroup using t-sql

does anyone know how to move a table (not just the indexes) to a different filegroup? I only find info on how to do this in EM. Even when I run a trace while doing it in EM I don't realy see anything in the trace that performs the action
I'm trying to schedule this for the nighttime - hence the reason I want to do it through t-sql.DBA72,
Do you have a clustered index on your table? If you do, you can move the
table to the new filegroup by issuing a create index statement WITH
DROP_EXISTING clause.
i.e. something like:
CREATE CLUSTERED INDEX mynewfilegroup ON mytable (col1, coln)
WITH DROP_EXISTING
ON filegroupname
Otherwise you will have to do what EM does. Create a temp table move your
data there, then create a new table on the new filegroup and copy it over.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"DBA72" <anonymous@.discussions.microsoft.com> wrote in message
news:AEF7FE8D-FB1A-4A58-81F5-149F65357BDB@.microsoft.com...
> does anyone know how to move a table (not just the indexes) to a different
filegroup? I only find info on how to do this in EM. Even when I run a trace
while doing it in EM I don't realy see anything in the trace that performs
the action?
> I'm trying to schedule this for the nighttime - hence the reason I want to
do it through t-sql.|||And if the table does not have a clustered index, you simply add one,
and then drop it again. If you drop the clustered index, the data will
stay on the same filegroup. I.e. something like:
CREATE CLUSTERED INDEX indextodrop ON mytable (col1, coln) ON
filegroupname
DROP INDEX mytable.indextodrop
Hope this helps,
Gert-Jan
Mark Allison wrote:
> DBA72,
> Do you have a clustered index on your table? If you do, you can move the
> table to the new filegroup by issuing a create index statement WITH
> DROP_EXISTING clause.
> i.e. something like:
> CREATE CLUSTERED INDEX mynewfilegroup ON mytable (col1, coln)
> WITH DROP_EXISTING
> ON filegroupname
> Otherwise you will have to do what EM does. Create a temp table move your
> data there, then create a new table on the new filegroup and copy it over.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "DBA72" <anonymous@.discussions.microsoft.com> wrote in message
> news:AEF7FE8D-FB1A-4A58-81F5-149F65357BDB@.microsoft.com...
> > does anyone know how to move a table (not just the indexes) to a different
> filegroup? I only find info on how to do this in EM. Even when I run a trace
> while doing it in EM I don't realy see anything in the trace that performs
> the action?
> >
> > I'm trying to schedule this for the nighttime - hence the reason I want to
> do it through t-sql.
--
(Please reply only to the newsgroup)

moving table from one drive to other in sql server 2000

Hi
I wanted the know the syntax, how we can move a particular table (lets say a
database having two filegroup in c, d drive) from c: drive to d: drive
without detaching that particular databaseHello,
If you have a clustered index on that table use CREATE INDEX with
DROP_EXISTING on your clustered index specifying new filegroup. THis will
move the table, data
and clustered index to the new filegroup (Data always follws the clustered
index). Incase if you do not have one then craete it temporarly on the new
file group
and drop it after.
Syntax:-
CREATE CLUSTERED INDEX <Clust_idx_name> ON Table_name
(COlumn_name) WITH DROP_EXISTING ON "NewFileGroup"
For non clustered index you could use DROP_Existing on the Create index
statement to move it to new filegroup.
Thanks
Hari
"shiva" <shiva@.discussions.microsoft.com> wrote in message
news:3EB80D03-5B8B-4032-88B5-5789B3966A5F@.microsoft.com...
> Hi
> I wanted the know the syntax, how we can move a particular table (lets say
> a
> database having two filegroup in c, d drive) from c: drive to d: drive
> without detaching that particular database

moving table from one drive to other in sql server 2000

Hi
I wanted the know the syntax, how we can move a particular table (lets say a
database having two filegroup in c, d drive) from c: drive to d: drive
without detaching that particular database
Hello,
If you have a clustered index on that table use CREATE INDEX with
DROP_EXISTING on your clustered index specifying new filegroup. THis will
move the table, data
and clustered index to the new filegroup (Data always follws the clustered
index). Incase if you do not have one then craete it temporarly on the new
file group
and drop it after.
Syntax:-
CREATE CLUSTERED INDEX <Clust_idx_name> ON Table_name
(COlumn_name) WITH DROP_EXISTING ON "NewFileGroup"
For non clustered index you could use DROP_Existing on the Create index
statement to move it to new filegroup.
Thanks
Hari
"shiva" <shiva@.discussions.microsoft.com> wrote in message
news:3EB80D03-5B8B-4032-88B5-5789B3966A5F@.microsoft.com...
> Hi
> I wanted the know the syntax, how we can move a particular table (lets say
> a
> database having two filegroup in c, d drive) from c: drive to d: drive
> without detaching that particular database

moving table from one drive to other in sql server 2000

Hi
I wanted the know the syntax, how we can move a particular table (lets say a
database having two filegroup in c, d drive) from c: drive to d: drive
without detaching that particular databaseHello,
If you have a clustered index on that table use CREATE INDEX with
DROP_EXISTING on your clustered index specifying new filegroup. THis will
move the table, data
and clustered index to the new filegroup (Data always follws the clustered
index). Incase if you do not have one then craete it temporarly on the new
file group
and drop it after.
Syntax:-
CREATE CLUSTERED INDEX <Clust_idx_name> ON Table_name
(COlumn_name) WITH DROP_EXISTING ON "NewFileGroup"
For non clustered index you could use DROP_Existing on the Create index
statement to move it to new filegroup.
Thanks
Hari
"shiva" <shiva@.discussions.microsoft.com> wrote in message
news:3EB80D03-5B8B-4032-88B5-5789B3966A5F@.microsoft.com...
> Hi
> I wanted the know the syntax, how we can move a particular table (lets say
> a
> database having two filegroup in c, d drive) from c: drive to d: drive
> without detaching that particular database