Moving a SQL DB to New Drive

I'm not a DBA, so if this question seems basic, I appologize. I am capable
of opening a command window and typing commands, without totally
understanding them, so I guess you could say I'm a DB Operator of sorts.
We recently had a SQL database installed on a new server. No other apps live
there. The Server has two logical drive arrays. C: is a mirrored pair and D:
is a RAID 5 three disk array.
The database was installed on the C: drive along with the SQL app itself.
The D: drive has 130 Gb free space with less than 5% already in use while
the primary drive is at 60% useage, and growing, with a max size of 20 Gb.
What we'd like to do is move the SQL database only to the D: drive.
1) How is this best accomplished?
2) Will the SQL app itself need to be reinstalled? (I don't think so, but
I'm not a DBA)
3) Will our clients' ODBC connection need to be redone to "map" to the new
home or will the
ODBC object autoupdate if the name is kept the same?
Thanks for the help!
1) http://support.microsoft.com/default...;en-us;Q224071
2) No
3) The actual location of the database files is irrelevant to ODBC, so you
don't have to change anything.
Jacco Schalkwijk
SQL Server MVP
"emanon" <NoSpam@.NoSpam.org> wrote in message
news:eV0XcLN9EHA.824@.TK2MSFTNGP11.phx.gbl...
> I'm not a DBA, so if this question seems basic, I appologize. I am capable
> of opening a command window and typing commands, without totally
> understanding them, so I guess you could say I'm a DB Operator of sorts.
> We recently had a SQL database installed on a new server. No other apps
> live
> there. The Server has two logical drive arrays. C: is a mirrored pair and
> D:
> is a RAID 5 three disk array.
> The database was installed on the C: drive along with the SQL app itself.
> The D: drive has 130 Gb free space with less than 5% already in use while
> the primary drive is at 60% useage, and growing, with a max size of 20
> Gb.
> What we'd like to do is move the SQL database only to the D: drive.
> 1) How is this best accomplished?
> 2) Will the SQL app itself need to be reinstalled? (I don't think so, but
> I'm not a DBA)
> 3) Will our clients' ODBC connection need to be redone to "map" to the
> new
> home or will the
> ODBC object autoupdate if the name is kept the same?
> Thanks for the help!
>
|||Thank you!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:eAQZdPN9EHA.3828@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> 1) http://support.microsoft.com/default...;en-us;Q224071
> 2) No
> 3) The actual location of the database files is irrelevant to ODBC, so you
> don't have to change anything.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "emanon" <NoSpam@.NoSpam.org> wrote in message
> news:eV0XcLN9EHA.824@.TK2MSFTNGP11.phx.gbl...
capable[vbcol=seagreen]
and[vbcol=seagreen]
itself.[vbcol=seagreen]
while[vbcol=seagreen]
but
>
sql

Moving a SQL DB to New Drive

I'm not a DBA, so if this question seems basic, I appologize. I am capable
of opening a command window and typing commands, without totally
understanding them, so I guess you could say I'm a DB Operator of sorts.
We recently had a SQL database installed on a new server. No other apps live
there. The Server has two logical drive arrays. C: is a mirrored pair and D:
is a RAID 5 three disk array.
The database was installed on the C: drive along with the SQL app itself.
The D: drive has 130 Gb free space with less than 5% already in use while
the primary drive is at 60% useage, and growing, with a max size of 20 Gb.
What we'd like to do is move the SQL database only to the D: drive.
1) How is this best accomplished?
2) Will the SQL app itself need to be reinstalled? (I don't think so, but
I'm not a DBA)
3) Will our clients' ODBC connection need to be redone to "map" to the new
home or will the
ODBC object autoupdate if the name is kept the same?
Thanks for the help!1) http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071
2) No
3) The actual location of the database files is irrelevant to ODBC, so you
don't have to change anything.
--
Jacco Schalkwijk
SQL Server MVP
"emanon" <NoSpam@.NoSpam.org> wrote in message
news:eV0XcLN9EHA.824@.TK2MSFTNGP11.phx.gbl...
> I'm not a DBA, so if this question seems basic, I appologize. I am capable
> of opening a command window and typing commands, without totally
> understanding them, so I guess you could say I'm a DB Operator of sorts.
> We recently had a SQL database installed on a new server. No other apps
> live
> there. The Server has two logical drive arrays. C: is a mirrored pair and
> D:
> is a RAID 5 three disk array.
> The database was installed on the C: drive along with the SQL app itself.
> The D: drive has 130 Gb free space with less than 5% already in use while
> the primary drive is at 60% useage, and growing, with a max size of 20
> Gb.
> What we'd like to do is move the SQL database only to the D: drive.
> 1) How is this best accomplished?
> 2) Will the SQL app itself need to be reinstalled? (I don't think so, but
> I'm not a DBA)
> 3) Will our clients' ODBC connection need to be redone to "map" to the
> new
> home or will the
> ODBC object autoupdate if the name is kept the same?
> Thanks for the help!
>|||Thank you!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eAQZdPN9EHA.3828@.TK2MSFTNGP09.phx.gbl...
> 1) http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071
> 2) No
> 3) The actual location of the database files is irrelevant to ODBC, so you
> don't have to change anything.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "emanon" <NoSpam@.NoSpam.org> wrote in message
> news:eV0XcLN9EHA.824@.TK2MSFTNGP11.phx.gbl...
> > I'm not a DBA, so if this question seems basic, I appologize. I am
capable
> > of opening a command window and typing commands, without totally
> > understanding them, so I guess you could say I'm a DB Operator of sorts.
> >
> > We recently had a SQL database installed on a new server. No other apps
> > live
> > there. The Server has two logical drive arrays. C: is a mirrored pair
and
> > D:
> > is a RAID 5 three disk array.
> >
> > The database was installed on the C: drive along with the SQL app
itself.
> > The D: drive has 130 Gb free space with less than 5% already in use
while
> > the primary drive is at 60% useage, and growing, with a max size of 20
> > Gb.
> >
> > What we'd like to do is move the SQL database only to the D: drive.
> >
> > 1) How is this best accomplished?
> >
> > 2) Will the SQL app itself need to be reinstalled? (I don't think so,
but
> > I'm not a DBA)
> >
> > 3) Will our clients' ODBC connection need to be redone to "map" to the
> > new
> > home or will the
> > ODBC object autoupdate if the name is kept the same?
> >
> > Thanks for the help!
> >
> >
>

Moving a SQL DB to New Drive

I'm not a DBA, so if this question seems basic, I appologize. I am capable
of opening a command window and typing commands, without totally
understanding them, so I guess you could say I'm a DB Operator of sorts.
We recently had a SQL database installed on a new server. No other apps live
there. The Server has two logical drive arrays. C: is a mirrored pair and D:
is a RAID 5 three disk array.
The database was installed on the C: drive along with the SQL app itself.
The D: drive has 130 Gb free space with less than 5% already in use while
the primary drive is at 60% useage, and growing, with a max size of 20 Gb.
What we'd like to do is move the SQL database only to the D: drive.
1) How is this best accomplished?
2) Will the SQL app itself need to be reinstalled? (I don't think so, but
I'm not a DBA)
3) Will our clients' ODBC connection need to be redone to "map" to the new
home or will the
ODBC object autoupdate if the name is kept the same?
Thanks for the help!1) http://support.microsoft.com/defaul...b;en-us;Q224071
2) No
3) The actual location of the database files is irrelevant to ODBC, so you
don't have to change anything.
Jacco Schalkwijk
SQL Server MVP
"emanon" <NoSpam@.NoSpam.org> wrote in message
news:eV0XcLN9EHA.824@.TK2MSFTNGP11.phx.gbl...
> I'm not a DBA, so if this question seems basic, I appologize. I am capable
> of opening a command window and typing commands, without totally
> understanding them, so I guess you could say I'm a DB Operator of sorts.
> We recently had a SQL database installed on a new server. No other apps
> live
> there. The Server has two logical drive arrays. C: is a mirrored pair and
> D:
> is a RAID 5 three disk array.
> The database was installed on the C: drive along with the SQL app itself.
> The D: drive has 130 Gb free space with less than 5% already in use while
> the primary drive is at 60% useage, and growing, with a max size of 20
> Gb.
> What we'd like to do is move the SQL database only to the D: drive.
> 1) How is this best accomplished?
> 2) Will the SQL app itself need to be reinstalled? (I don't think so, but
> I'm not a DBA)
> 3) Will our clients' ODBC connection need to be redone to "map" to the
> new
> home or will the
> ODBC object autoupdate if the name is kept the same?
> Thanks for the help!
>|||Thank you!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eAQZdPN9EHA.3828@.TK2MSFTNGP09.phx.gbl...
> 1) http://support.microsoft.com/defaul...b;en-us;Q224071
> 2) No
> 3) The actual location of the database files is irrelevant to ODBC, so you
> don't have to change anything.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "emanon" <NoSpam@.NoSpam.org> wrote in message
> news:eV0XcLN9EHA.824@.TK2MSFTNGP11.phx.gbl...
capable[vbcol=seagreen]
and[vbcol=seagreen]
itself.[vbcol=seagreen]
while[vbcol=seagreen]
but[vbcol=seagreen]
>

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

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 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 format.
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 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
>

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...
>
>

Move MSDB

Hi,
Is there a way to move the MSDB database files to a different disk? A detach
command is disabled in the EM, but would it work from the QA? And would this
work?
TIA
GerryGerry,
Yes. Read KB 224071. It has instruction on how to move msdb. Follow the instructions closely.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Gerry Laenen" <gerry_laenen@.NOSPAMhotmail.com> wrote in message
news:3fa0cc78$0$264$ba620e4c@.reader1.news.skynet.be...
> Hi,
> Is there a way to move the MSDB database files to a different disk? A detach
> command is disabled in the EM, but would it work from the QA? And would this
> work?
> TIA
> Gerry
>