Move database file: alter

Showing posts with label alter. Show all posts

Wednesday, March 28, 2012

moving a table to another filegroup in T-SQL

Hi
I want to move a table to another filegroup using T-SQL (alter table ...).
In BOL i do not find an example of this case. I trried something like
alter <table>
move to <filegroup>
but it does not work
Can somebody help me please. Thanks Waldo
If your answer to Tibors post is Yes then,
Easiest method is to re-create the clustered index in new file group. This
will move the table to the new file group automatically.
This means data will be moved to new file group once you move the clustered
Index
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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uROKsdrRHHA.2252@.TK2MSFTNGP02.phx.gbl...
> Does the table currently have a clustered index?
> Do you want the table to have a clustered index after you have moved it?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Waldo" <Waldo@.discussions.microsoft.com> wrote in message
> news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
>

moving a table to another filegroup in T-SQL

Hi
I want to move a table to another filegroup using T-SQL (alter table ...).
In BOL i do not find an example of this case. I trried something like
alter <table>
move to <filegroup>
but it does not work
Can somebody help me please. Thanks WaldoDoes the table currently have a clustered index?
Do you want the table to have a clustered index after you have moved it?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Waldo" <Waldo@.discussions.microsoft.com> wrote in message
news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
> Hi
> I want to move a table to another filegroup using T-SQL (alter table ...).
> In BOL i do not find an example of this case. I trried something like
> alter <table>
> move to <filegroup>
> but it does not work
> Can somebody help me please. Thanks Waldo|||If your answer to Tibors post is Yes then,
Easiest method is to re-create the clustered index in new file group. This
will move the table to the new file group automatically.
This means data will be moved to new file group once you move the clustered
Index
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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uROKsdrRHHA.2252@.TK2MSFTNGP02.phx.gbl...
> Does the table currently have a clustered index?
> Do you want the table to have a clustered index after you have moved it?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Waldo" <Waldo@.discussions.microsoft.com> wrote in message
> news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
>

moving a table to another filegroup in T-SQL

Hi
I want to move a table to another filegroup using T-SQL (alter table ...).
In BOL i do not find an example of this case. I trried something like
alter <table>
move to <filegroup>
but it does not work
Can somebody help me please. Thanks WaldoDoes the table currently have a clustered index?
Do you want the table to have a clustered index after you have moved it?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Waldo" <Waldo@.discussions.microsoft.com> wrote in message
news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
> Hi
> I want to move a table to another filegroup using T-SQL (alter table ...).
> In BOL i do not find an example of this case. I trried something like
> alter <table>
> move to <filegroup>
> but it does not work
> Can somebody help me please. Thanks Waldo|||If your answer to Tibors post is Yes then,
Easiest method is to re-create the clustered index in new file group. This
will move the table to the new file group automatically.
This means data will be moved to new file group once you move the clustered
Index
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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uROKsdrRHHA.2252@.TK2MSFTNGP02.phx.gbl...
> Does the table currently have a clustered index?
> Do you want the table to have a clustered index after you have moved it?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Waldo" <Waldo@.discussions.microsoft.com> wrote in message
> news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
>> Hi
>> I want to move a table to another filegroup using T-SQL (alter table
>> ...).
>> In BOL i do not find an example of this case. I trried something like
>> alter <table>
>> move to <filegroup>
>> but it does not work
>> Can somebody help me please. Thanks Waldo
>sql

Moving a log file

Hi,
I know that I can detach a database, move the log file(s) and then
re-attach, but what is the
syntax to do it with Alter Database (or is it even possible)? I have
searched high and low and only found examples
that use sp_detach_db
Thanks,
DanFor 2000, no syntax using ALTER DATABASE except for tempdb.
For 2005, you can use ALTER DATABASE. See below 2005 BOL URL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/89f01b10-5fae-4ed8-b0fb-a4b9f540fd28.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:%2328kfSc%23FHA.600@.tk2msftngp13.phx.gbl...
> Hi,
> I know that I can detach a database, move the log file(s) and then re-attach, but what is the
> syntax to do it with Alter Database (or is it even possible)? I have searched high and low and
> only found examples
> that use sp_detach_db
> Thanks,
> Dan
>
>|||Thanks Tibor,
I know I had used it for tempdb and thought maybe I could use it for
'regular' db's.
Dan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ODXUvfc%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
> For 2000, no syntax using ALTER DATABASE except for tempdb.
> For 2005, you can use ALTER DATABASE. See below 2005 BOL URL:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/89f01b10-5fae-4ed8-b0fb-a4b9f540fd28.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
> news:%2328kfSc%23FHA.600@.tk2msftngp13.phx.gbl...
>> Hi,
>> I know that I can detach a database, move the log file(s) and then
>> re-attach, but what is the
>> syntax to do it with Alter Database (or is it even possible)? I have
>> searched high and low and only found examples
>> that use sp_detach_db
>> Thanks,
>> Dan
>>
>|||Hi Dan
You can only use it for tempdb because tempdb is rebuilt every time you
restart your SQL Server. And in fact, when you use it for tempdb, it doesn't
take effect until you stop and restart.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:%23rjulyc%23FHA.2816@.tk2msftngp13.phx.gbl...
> Thanks Tibor,
> I know I had used it for tempdb and thought maybe I could use it for
> 'regular' db's.
>
> Dan
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ODXUvfc%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
>> For 2000, no syntax using ALTER DATABASE except for tempdb.
>> For 2005, you can use ALTER DATABASE. See below 2005 BOL URL:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/89f01b10-5fae-4ed8-b0fb-a4b9f540fd28.htm
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
>> news:%2328kfSc%23FHA.600@.tk2msftngp13.phx.gbl...
>> Hi,
>> I know that I can detach a database, move the log file(s) and then
>> re-attach, but what is the
>> syntax to do it with Alter Database (or is it even possible)? I have
>> searched high and low and only found examples
>> that use sp_detach_db
>> Thanks,
>> Dan
>>
>>
>
>

Moving a log file

Wednesday, March 21, 2012

Move table to partition schema.

I have table with several million rows, which I would like to move to
partition schema.
I've found this option is present in ALTER TABLE command. However, this
command
alter table TaleName move to ps_Schema ( PartitionColumnName)
resulted in error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move'.
I assume the syntax is wrong, but how to accomplish this task ? What is the
correct syntax?
Thank you very much.I assume you are using SQL Server 2005.
Please read the full definition of ALTER TABLE again.
The MOVE can only be used as part of dropping a clustered index, so it is
the clustered index that is moving. The clustered index needs to have been
created as part of a primary key or unique constraint.
Make sure you already have the partition scheme defined.
The syntax would be something like this (untested):
ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
WITH MOVE TO ps_Schema ( PartitionColumnName)
(I am also assuming you are aware a partition scheme has nothing to do with
schema, so that your name ps_schema is just a typo.)
--
HTH
Kalen Delaney, SQL Server MVP
"BP" <BP@.discussions.microsoft.com> wrote in message
news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>I have table with several million rows, which I would like to move to
> partition schema.
> I've found this option is present in ALTER TABLE command. However, this
> command
> alter table TaleName move to ps_Schema ( PartitionColumnName)
> resulted in error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'move'.
> I assume the syntax is wrong, but how to accomplish this task ? What is
> the
> correct syntax?
> Thank you very much.
>|||Kalen,
Thank you very much. I am admired with your articles and books.
I am waiting to buy your new book.
The correct syntax is:
ALTER TABLE dbo.Test DROP CONSTRAINT pk_Test
WITH (MOVE TO ps_OrderDate (OrigOrderDT));
Thank you again.
"Kalen Delaney" wrote:
> I assume you are using SQL Server 2005.
> Please read the full definition of ALTER TABLE again.
> The MOVE can only be used as part of dropping a clustered index, so it is
> the clustered index that is moving. The clustered index needs to have been
> created as part of a primary key or unique constraint.
> Make sure you already have the partition scheme defined.
> The syntax would be something like this (untested):
> ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
> WITH MOVE TO ps_Schema ( PartitionColumnName)
> (I am also assuming you are aware a partition scheme has nothing to do with
> schema, so that your name ps_schema is just a typo.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "BP" <BP@.discussions.microsoft.com> wrote in message
> news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
> >I have table with several million rows, which I would like to move to
> > partition schema.
> >
> > I've found this option is present in ALTER TABLE command. However, this
> > command
> >
> > alter table TaleName move to ps_Schema ( PartitionColumnName)
> >
> > resulted in error:
> >
> > Msg 102, Level 15, State 1, Line 1
> > Incorrect syntax near 'move'.
> >
> > I assume the syntax is wrong, but how to accomplish this task ? What is
> > the
> > correct syntax?
> >
> > Thank you very much.
> >
>
>

Move table to partition schema.

I have table with several million rows, which I would like to move to
partition schema.
I've found this option is present in ALTER TABLE command. However, this
command
alter table TaleName move to ps_Schema ( PartitionColumnName)
resulted in error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move'.
I assume the syntax is wrong, but how to accomplish this task ? What is the
correct syntax?
Thank you very much.I assume you are using SQL Server 2005.
Please read the full definition of ALTER TABLE again.
The MOVE can only be used as part of dropping a clustered index, so it is
the clustered index that is moving. The clustered index needs to have been
created as part of a primary key or unique constraint.
Make sure you already have the partition scheme defined.
The syntax would be something like this (untested):
ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
WITH MOVE TO ps_Schema ( PartitionColumnName)
(I am also assuming you are aware a partition scheme has nothing to do with
schema, so that your name ps_schema is just a typo.)
--
HTH
Kalen Delaney, SQL Server MVP
"BP" <BP@.discussions.microsoft.com> wrote in message
news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>I have table with several million rows, which I would like to move to
> partition schema.
> I've found this option is present in ALTER TABLE command. However, this
> command
> alter table TaleName move to ps_Schema ( PartitionColumnName)
> resulted in error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'move'.
> I assume the syntax is wrong, but how to accomplish this task ? What is
> the
> correct syntax?
> Thank you very much.
>|||Kalen,
Thank you very much. I am admired with your articles and books.
I am waiting to buy your new book.
The correct syntax is:
ALTER TABLE dbo.Test DROP CONSTRAINT pk_Test
WITH (MOVE TO ps_OrderDate (OrigOrderDT));
Thank you again.
"Kalen Delaney" wrote:

> I assume you are using SQL Server 2005.
> Please read the full definition of ALTER TABLE again.
> The MOVE can only be used as part of dropping a clustered index, so it is
> the clustered index that is moving. The clustered index needs to have been
> created as part of a primary key or unique constraint.
> Make sure you already have the partition scheme defined.
> The syntax would be something like this (untested):
> ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
> WITH MOVE TO ps_Schema ( PartitionColumnName)
> (I am also assuming you are aware a partition scheme has nothing to do wit
h
> schema, so that your name ps_schema is just a typo.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "BP" <BP@.discussions.microsoft.com> wrote in message
> news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>
>

Monday, March 19, 2012

move table to another filegroup

alter table db.schme.table drop move to filegroup1?

this doesnot work

thanks

your syntax looks wrong

try:

altertable dbo.Gladiators

dropconstraint pk_Gladiators_int1

WITH(moveto'filegroup1')

|||

In order to move a table to another filegroup, use the statement "create index" with the option "with drop_existing", to recreate the clustered index, and specify the new filegroup. See "create index" in BOL.

AMB

Friday, March 9, 2012

move indexes via ALTER INDEX

I was checking out an indexes property via SQL 2005 Management Studio
and it looks like I can move the index to other filegroups via the
drop down.
I checked the BOL ALTER INDEX and I didnt see an argument for this
action. I see stuff about PARTITION, but I think that is for row
partitioning??
I want to move existing indexes from the PRIMARY filegroup to a new
file group just for indexes, which is called INDEXES.
Can this be done via ALTER INDEX or some other way?
TIA
RobCheck out the CREATE INDEX ... WITH DROP_EXISTING syntax where you specify
the filegroup to create the new index on.

--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173752583.908449.87100@.30g2000cwc.googlegrou ps.com...

Quote:

Originally Posted by

>I was checking out an indexes property via SQL 2005 Management Studio
and it looks like I can move the index to other filegroups via the
drop down.
I checked the BOL ALTER INDEX and I didnt see an argument for this
action. I see stuff about PARTITION, but I think that is for row
partitioning??
I want to move existing indexes from the PRIMARY filegroup to a new
file group just for indexes, which is called INDEXES.
Can this be done via ALTER INDEX or some other way?
TIA
Rob
>

Wednesday, March 7, 2012

Move distribution data/log files to another drive

I haven't found the definitive answer on how or if this can be done without removing replication. I'm thinking ALTER DATABASE modify_file is the way to go. Anybody know if this will work or a better way to go about it?

Thanks

"Alter database" should work. There is a similar posting.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1619608&SiteID=1

Gary

|||I saw that one. The client ended up adding a file to the new drive and shrinking the original.

Thanks

Wednesday, March 28, 2012

moving a table to another filegroup in T-SQL

moving a table to another filegroup in T-SQL

moving a table to another filegroup in T-SQL

Moving a log file

Moving a log file

Moving a log file

Wednesday, March 21, 2012

Move table to partition schema.

Move table to partition schema.

Monday, March 19, 2012

move table to another filegroup

Friday, March 9, 2012

move indexes via ALTER INDEX

Wednesday, March 7, 2012

Move distribution data/log files to another drive

Move database file

Blog Archive

About Me