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 Database

Hi

I want to transfer a database from one PC to another. (Both running SQL Server 2005 express)

I have copied the files (SQL Server Database Primary Data File and SQL Server Database Transaction Log File) from the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder of the source PC to the destination PC (Same folder).

I was under the impression that using Microsoft SQL Server 2005 - SQL Server Management Studio Express these files would be picked up and could be used, do I infact need to do something to attach the files/database so that they will appear in SQL Server management?

If you are moving the db;s you need to'detach' them first, then copy the files to the new location, then'attach' them back. check out BOL on how to attach/detach DB's.|||

Thanks

I have just tried doing this, I opened a sql command window on (local)SQLEXPRESS.master - SQLQuery1.sql and than this script:

EXEC

sp_attach_db @.dbname='pramsetc',

@.filename1

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog.mdf',

@.filename2

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog_log.ldf'

This seemed to run okay, but I cant now find my database anywhere in Object Explorer in Management Studio, however the database must of been created somwhere as when I try running the sql again I get:

Msg 1801, Level 16, State 3, Line 1

Database 'blog' already exists.

Any pointers as to where I am going wrong?FONT>

|||

Apologies correction:

Thanks

I have just tried doing this, I opened a sql command window on (local)SQLEXPRESS.master - SQLQuery1.sql and than this script:

EXEC

sp_attach_db @.dbname='blog',

@.filename1

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog.mdf',

@.filename2

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog_log.ldf'

This seemed to run okay, but I cant now find my database anywhere in Object Explorer in Management Studio, however the database must of been created somwhere as when I try running the sql again I get:

Msg 1801, Level 16, State 3, Line 1

Database 'blog' already exists.

Any pointers as to where I am going wrong?

|||Did you detach the DB before attaching it? If you did, refresh your databases list and you could probably see the new db in the list.|||

No I didnt know I needed to do that when I copied them across. I will do that now, just as a matter of interest why does that make a difference, what does the detach do that affects the file being attached locally?

Thanks

|||I am puzzled as how it let you copy the files without getting an "Access denied" or "File in use" error. (2) On the server where you are restoring, refresh the databases on the enterprise maanger. Do you see the 'Blob' db? If you already have one, you need to give a different name while restoring.|||I will try detaching it first, thanks

Moving 6.5 database

HI

I have to move a production 6.5 database to another server (also 6.5) in wich other dbs already exist.
1. Can I just backup and restore on the other server ?
2. Do I have to check for the same service packs on both?
3. Are there other ways to do it?

Any help will be apreciated, since I can't find much info about this version.

Thank youIt's been awhile since I've used 6.5, however I know in the pass I've moved a dump over from one machine to another, a few notes:[list=a]
Make sure both machines where setup with the same CodePage
You'll have to resync any UserIDs in the database sysusers, to those in Master..syslogins, I believe Microsoft have a stored proc that does this
Recompile all of your procs, we use to have a problem with this. Use a simple cursor to loop through user tables issuing a 'SP_RECOMPILE' on each table
[/list=a]

moveing data

Hi
I need to move data from few tables in sql server 2000 SP4 to sql server
2005 database. I can select the data using a select command from all the
tables and save it in a text file.
Now When I try to import it into destination server from txt file using SQL
server import export wizard, It is importing it in one column only. How to
define column delimiter in the file or store the output of select command
from sql server 2000 in a format which is easy to upload in sql server 20005
in right format (columns). The file is big and can not stored in excel format.
Thanks
jpr
I have set query options. It works.
ontario, canada
"db" wrote:

> Hi
> I need to move data from few tables in sql server 2000 SP4 to sql server
> 2005 database. I can select the data using a select command from all the
> tables and save it in a text file.
> Now When I try to import it into destination server from txt file using SQL
> server import export wizard, It is importing it in one column only. How to
> define column delimiter in the file or store the output of select command
> from sql server 2000 in a format which is easy to upload in sql server 20005
> in right format (columns). The file is big and can not stored in excel format.
> Thanks
> jpr
>

Moveing data

Hi
I need to move data from few tables in sql server 2000 SP4 to sql server
2005 database. I can select the data using a select command from all the
tables and save it in a text file.
Now When I try to import it into destination server from txt file using SQL
server import export wizard, It is importing it in one column only. How to
define column delimiter in the file. The file is big and can not stoted in
excel format.
Thanks
jpr
I have set query options. It works.
"db" wrote:

> Hi
> I need to move data from few tables in sql server 2000 SP4 to sql server
> 2005 database. I can select the data using a select command from all the
> tables and save it in a text file.
> Now When I try to import it into destination server from txt file using SQL
> server import export wizard, It is importing it in one column only. How to
> define column delimiter in the file. The file is big and can not stoted in
> excel format.
> Thanks
> jpr
>

moveing data

Hi
I need to move data from few tables in sql server 2000 SP4 to sql server
2005 database. I can select the data using a select command from all the
tables and save it in a text file.
Now When I try to import it into destination server from txt file using SQL
server import export wizard, It is importing it in one column only. How to
define column delimiter in the file or store the output of select command
from sql server 2000 in a format which is easy to upload in sql server 20005
in right format (columns). The file is big and can not stored in excel forma
t.
Thanks
jprI have set query options. It works.
--
ontario, canada
"db" wrote:

> Hi
> I need to move data from few tables in sql server 2000 SP4 to sql server
> 2005 database. I can select the data using a select command from all the
> tables and save it in a text file.
> Now When I try to import it into destination server from txt file using SQ
L
> server import export wizard, It is importing it in one column only. How to
> define column delimiter in the file or store the output of select command
> from sql server 2000 in a format which is easy to upload in sql server 200
05
> in right format (columns). The file is big and can not stored in excel for
mat.
> Thanks
> jpr
>

Moveing data

Hi
I need to move data from few tables in sql server 2000 SP4 to sql server
2005 database. I can select the data using a select command from all the
tables and save it in a text file.
Now When I try to import it into destination server from txt file using SQL
server import export wizard, It is importing it in one column only. How to
define column delimiter in the file. The file is big and can not stoted in
excel format.
Thanks
jprI have set query options. It works.
--
"db" wrote:

> Hi
> I need to move data from few tables in sql server 2000 SP4 to sql server
> 2005 database. I can select the data using a select command from all the
> tables and save it in a text file.
> Now When I try to import it into destination server from txt file using SQ
L
> server import export wizard, It is importing it in one column only. How to
> define column delimiter in the file. The file is big and can not stoted in
> excel format.
> Thanks
> jpr
>sql

Move transaction logs in SQL cluster

Hi
I need to move the transaction logs for all the sql db's (including master,
model and user db's etc) to a seperate drive. Does anyone have any experience
doing this in a sql cluster?
Thanks
Parky
Parky wrote:
> Hi
> I need to move the transaction logs for all the sql db's (including
> master, model and user db's etc) to a seperate drive. Does anyone
> have any experience doing this in a sql cluster?
There is no difference if you move t-log in a single box machine or in a
cluster environment.
You have to pay attention to move db files ONLY on shared disk and modify,
using EM, the switch -l in startup properties of SQL Server and assign them
the new location of t-log of master database

> Thanks
Bye
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com
http://italy.mvps.org
|||Hello Parky,
I agree with Luca. There is no difference between moving the db files on a standalone SQL server v/s clustered SQL Server. One difference is that the shared dirve where you want to move the db files should be in
the dependency list of SQL Server resource. (For Ex. Say you want to move the Tlogs to a shared drive F:. Make sure that SQL Server resource has F: as a dependency. If not you will have have make it. Note that
this requires a downtime as you will have to take SQL Server resource offline, add the shared drive as a dependency and then take SQL Server online. Then you can use your usual steps (see
http://support.microsoft.com/kb/224071/EN-US/
INF: Moving SQL Server Databases to a New Location using Detach/Attach )
If you have never done this on a cluster, I will recommend you to practice the steps on a test SQL Cluster or a cluster using virtual technology.
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||I would leave the System databases and there log file on one drive and split
the user databases to two diffrent databases, that way is you losse the log
drive you can still get sql server running and manage it to recreate the user
databases from the datafiles with diffrent location of the log files.
"Uttam Parui[MS]" wrote:

> Hello Parky,
> I agree with Luca. There is no difference between moving the db files on a standalone SQL server v/s clustered SQL Server. One difference is that the shared dirve where you want to move the db files should be in
> the dependency list of SQL Server resource. (For Ex. Say you want to move the Tlogs to a shared drive F:. Make sure that SQL Server resource has F: as a dependency. If not you will have have make it. Note that
> this requires a downtime as you will have to take SQL Server resource offline, add the shared drive as a dependency and then take SQL Server online. Then you can use your usual steps (see
> http://support.microsoft.com/kb/224071/EN-US/
> INF: Moving SQL Server Databases to a New Location using Detach/Attach )
> If you have never done this on a cluster, I will recommend you to practice the steps on a test SQL Cluster or a cluster using virtual technology.
> HTH,
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>

Move Repliaction

Hi
I have 1 publisher that replicates well to to Server1 and doesn't replicate
well to Server2 I would like to simply move the database and the
Subscription from Server1 to Server2 (that will save me the initial phase of
the subscription of copying the database) (Push subscription)
Is that possible and how?
Thank you,
Samuel
You would have to script it out and recreate it on server 2.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:uN$353T5GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have 1 publisher that replicates well to to Server1 and doesn't
> replicate well to Server2 I would like to simply move the database and the
> Subscription from Server1 to Server2 (that will save me the initial phase
> of the subscription of copying the database) (Push subscription)
> Is that possible and how?
> Thank you,
> Samuel
>
>
|||Samuel,
provided you have an up-to-date copy of the database on the subscriber, you
could do a nosync initialization:
http://www.replicationanswers.com/No...alizations.asp
http://www.replicationanswers.com/NoSyncOn2005.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Move MS SQL 2005 Express Edition Data Path

Hi!
I just installed Web Developer express edition on my Windows 2000 server
machine. I just wanted to know how may I change the default data path of MS
SQL Server 2005 express edition.
Me
If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRoot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.
|||If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi crosoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRo ot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.

Move MS SQL 2005 Express Edition Data Path

Hi!
I just installed Web Developer express edition on my Windows 2000 server
machine. I just wanted to know how may I change the default data path of MS
SQL Server 2005 express edition.
MeIf you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MSSQL.1\Setup\SQLDataRoot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.|||If you already installed it, look in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi crosoft\Microsoft SQL
Server\MSSQL.1\Setup\SQLDataRo ot
Changing this will require a restart of the SQL Server service.
HTH, jens Suessmeyer.

Move db

Hi
I have WIn2003 SP1+SQL2000 SP3.
Al the DB sites on drive D, I installed new HD Edrive.
I need to move all the DB to the new drive.
What is the best to do that?
Than'x
Shay
http://www.webservertalk.com/archive132-2004-7-330935.html
Aaron Bertrand
SQL Server MVP
"S" <s@.s.com> wrote in message news:uF11vLf2HHA.5740@.TK2MSFTNGP04.phx.gbl...
> Hi
> I have WIn2003 SP1+SQL2000 SP3.
> Al the DB sites on drive D, I installed new HD Edrive.
> I need to move all the DB to the new drive.
> What is the best to do that?
> Than'x
> Shay
>
|||Search the Books On Line (BOL) for Detach and Attach:
Quoting BOL:
To move a database, or database file, to another server or disk:
1) Detach the database.
2) Move the database file(s) to the other server or disk.
3) Attach the database specifying the new location of the moved
file(s).
My only additional comment is that I would have really good backups
first, and I would COPY rather than MOVE, only getting rid of the
oroginal files after the database is working from the new location.
Roy Harvey
Beacon Falls, CT
On Wed, 8 Aug 2007 22:32:15 +0200, "S" <s@.s.com> wrote:

>Hi
>I have WIn2003 SP1+SQL2000 SP3.
>Al the DB sites on drive D, I installed new HD Edrive.
>I need to move all the DB to the new drive.
>What is the best to do that?
>Than'x
>Shay
>
|||On Aug 8, 2:44 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Search the Books On Line (BOL) for Detach and Attach:
> Quoting BOL:
> To move a database, or database file, to another server or disk:
> 1) Detach the database.
> 2) Move the database file(s) to the other server or disk.
> 3) Attach the database specifying the new location of the moved
> file(s).
> My only additional comment is that I would have really good backups
> first, and I would COPY rather than MOVE, only getting rid of the
> oroginal files after the database is working from the new location.
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 8 Aug 2007 22:32:15 +0200, "S" <s...@.s.com> wrote:
>
>
>
> - Show quoted text -
I agree with the detach and attach method in this case. You should
simply detach the database(s) you wish to move, move the files to
their new location, the simply run the attach method. It will ask you
to browse for your .mdf and .ldf files and voila, you will be done.

Move db

Hi
I have WIn2003 SP1+SQL2000 SP3.
Al the DB sites on drive D, I installed new HD Edrive.
I need to move all the DB to the new drive.
What is the best to do that?
Than'x
Shayhttp://www.webservertalk.com/archiv...4-7-330935.html
Aaron Bertrand
SQL Server MVP
"S" <s@.s.com> wrote in message news:uF11vLf2HHA.5740@.TK2MSFTNGP04.phx.gbl...
> Hi
> I have WIn2003 SP1+SQL2000 SP3.
> Al the DB sites on drive D, I installed new HD Edrive.
> I need to move all the DB to the new drive.
> What is the best to do that?
> Than'x
> Shay
>|||Search the Books On Line (BOL) for Detach and Attach:
Quoting BOL:
To move a database, or database file, to another server or disk:
1) Detach the database.
2) Move the database file(s) to the other server or disk.
3) Attach the database specifying the new location of the moved
file(s).
My only additional comment is that I would have really good backups
first, and I would COPY rather than MOVE, only getting rid of the
oroginal files after the database is working from the new location.
Roy Harvey
Beacon Falls, CT
On Wed, 8 Aug 2007 22:32:15 +0200, "S" <s@.s.com> wrote:

>Hi
>I have WIn2003 SP1+SQL2000 SP3.
>Al the DB sites on drive D, I installed new HD Edrive.
>I need to move all the DB to the new drive.
>What is the best to do that?
>Than'x
>Shay
>|||On Aug 8, 2:44 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Search the Books On Line (BOL) for Detach and Attach:
> Quoting BOL:
> To move a database, or database file, to another server or disk:
> 1) Detach the database.
> 2) Move the database file(s) to the other server or disk.
> 3) Attach the database specifying the new location of the moved
> file(s).
> My only additional comment is that I would have really good backups
> first, and I would COPY rather than MOVE, only getting rid of the
> oroginal files after the database is working from the new location.
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 8 Aug 2007 22:32:15 +0200, "S" <s...@.s.com> wrote:
>
>
>
>
>
> - Show quoted text -
I agree with the detach and attach method in this case. You should
simply detach the database(s) you wish to move, move the files to
their new location, the simply run the attach method. It will ask you
to browse for your .mdf and .ldf files and voila, you will be done.

Move Data from Schema to schema

Hi
I have two schemas in same database here is the detail
Source
schema : Imac_admin
table name :Part
Column name : Part ID
Target :
Schema - Customer
Table : product_xref
Coumn : Webpartid
Need to move data from Part id to webpart id"san" <skrupareliya@.gmail.com> wrote in message
news:1142026409.975993.305480@.i39g2000cwa.googlegroups.com...
> Hi
>
> I have two schemas in same database here is the detail
> Source
> schema : Imac_admin
> table name :Part
> Column name : Part ID
> Target :
> Schema - Customer
> Table : product_xref
> Coumn : Webpartid
> Need to move data from Part id to webpart id
>
I guess you'll want an UPDATE statement with two-part object names:
UPDATE Customer.product_xref
SET webpartid =
(SELECT part_id
FROM Imac_admin.Part
WHERE ... /* ' not specified */)
WHERE ... /* ' not specified */ ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--