moving mdf files

hi all,
could someone help me out on this question? i have a database made up of 3
.mdf files. the files are all on the same drive of a raid 10 so we need to
move them to different drives, as it is obvious that we are not taking
advantage of the hardware. how do i move the mdf files? what is the best
possible way since the db is relativly big and the time frame we have to
make this movement is small?
thanks in advance.
hector quintanillai am sorry for answering my own post, but i didn't see this part while
reading BoL:
If the database you are moving has additional data or log files, specify all
of them in a comma-delimited list in the sp_attach_db stored procedure. The
sp_detach_db procedure does not change no matter how many files the database
contains because it does not list them.
thanks and sorry!
"hector quintanilla" <hquintanilla@.cyberworksNOSPAM.com.mx> wrote in message
news:erashzznDHA.2652@.TK2MSFTNGP09.phx.gbl...
> hi all,
> could someone help me out on this question? i have a database made up of
3
> .mdf files. the files are all on the same drive of a raid 10 so we need
to
> move them to different drives, as it is obvious that we are not taking
> advantage of the hardware. how do i move the mdf files? what is the best
> possible way since the db is relativly big and the time frame we have to
> make this movement is small?
> thanks in advance.
> hector quintanilla
>|||Hi Hector.
Nothing to be sorry about!
If it's ok to take the database offline during the move, then using
sp_detach_db etc is an ok approach. There's a limit of 16 files with that
approach, but you're no-where near that.
There are also other options to do this online if needed, although they may
be slower & it sounds like you're probably ok with going offline anway.
Regards,
Greg Linwood
SQL Server MVP
"hector quintanilla" <hquintanilla@.cyberworksNOSPAM.com.mx> wrote in message
news:uCpbs9znDHA.2064@.TK2MSFTNGP11.phx.gbl...
> i am sorry for answering my own post, but i didn't see this part while
> reading BoL:
> If the database you are moving has additional data or log files, specify
all
> of them in a comma-delimited list in the sp_attach_db stored procedure.
The
> sp_detach_db procedure does not change no matter how many files the
database
> contains because it does not list them.
> thanks and sorry!
> "hector quintanilla" <hquintanilla@.cyberworksNOSPAM.com.mx> wrote in
message
> news:erashzznDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > hi all,
> >
> > could someone help me out on this question? i have a database made up
of
> 3
> > .mdf files. the files are all on the same drive of a raid 10 so we need
> to
> > move them to different drives, as it is obvious that we are not taking
> > advantage of the hardware. how do i move the mdf files? what is the
best
> > possible way since the db is relativly big and the time frame we have to
> > make this movement is small?
> >
> > thanks in advance.
> >
> > hector quintanilla
> >
> >
>|||greg,
thanks for the reply. could you be more specific about the way to do this
change online? maybe a url? it may be ok to do it online, but it will
depend on the clients need.
thanks in advance
hector quintanilla
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:urSI0F0nDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Hi Hector.
> Nothing to be sorry about!
> If it's ok to take the database offline during the move, then using
> sp_detach_db etc is an ok approach. There's a limit of 16 files with that
> approach, but you're no-where near that.
> There are also other options to do this online if needed, although they
may
> be slower & it sounds like you're probably ok with going offline anway.
> Regards,
> Greg Linwood
> SQL Server MVP
> "hector quintanilla" <hquintanilla@.cyberworksNOSPAM.com.mx> wrote in
message
> news:uCpbs9znDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > i am sorry for answering my own post, but i didn't see this part while
> > reading BoL:
> >
> > If the database you are moving has additional data or log files, specify
> all
> > of them in a comma-delimited list in the sp_attach_db stored procedure.
> The
> > sp_detach_db procedure does not change no matter how many files the
> database
> > contains because it does not list them.
> >
> > thanks and sorry!
> >
> > "hector quintanilla" <hquintanilla@.cyberworksNOSPAM.com.mx> wrote in
> message
> > news:erashzznDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > > hi all,
> > >
> > > could someone help me out on this question? i have a database made up
> of
> > 3
> > > .mdf files. the files are all on the same drive of a raid 10 so we
need
> > to
> > > move them to different drives, as it is obvious that we are not taking
> > > advantage of the hardware. how do i move the mdf files? what is the
> best
> > > possible way since the db is relativly big and the time frame we have
to
> > > make this movement is small?
> > >
> > > thanks in advance.
> > >
> > > hector quintanilla
> > >
> > >
> >
> >
>|||It can be done a few ways - depending on requirements / size etc.
If the database is not a VLDBMS (ie huge) you may be able to simply create a
new file on the target volume / drive, start a transaction & take the steps
to move the object/s from the old file over there. This may even be as
simple as begin transaction, rename existing objects (tables, stored procs
etc), create new object/s (tables, stored proc on old file) on new file,
select into, index maintenance, drop old table, commit transaction. Then if
all objects are off the old file successfully, drop the file & delete it
from the file system.
If you have many tables, it may be worth investing time in building DTS
packages that perform the work in parallel. Basically, if you don't have an
online need, it's usually not worth going through all this as there's
analysis, planning & preparation that isn't worth it if you don't really
need to do it online.
I was mainly pointing out that there are online options which may be worth
looking into but only if you know you need to. sp_detach_db is certainly
going to be an offline operation.
I don't have a specific urls unfortunately, although I'm hoping someone else
who read the thread may know of one.. Perhaps post a new thread in case
others ignore this now it's been responded to.
Regards,
Greg Linwood
SQL Server MVP
"hector quintanilla" <hquintanilla@.cyberworksNOSPAM.com.mx> wrote in message
news:edTfCa0nDHA.1676@.TK2MSFTNGP09.phx.gbl...
> greg,
> thanks for the reply. could you be more specific about the way to do this
> change online? maybe a url? it may be ok to do it online, but it will
> depend on the clients need.
> thanks in advance
> hector quintanilla
> "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
> message news:urSI0F0nDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > Hi Hector.
> >
> > Nothing to be sorry about!
> >
> > If it's ok to take the database offline during the move, then using
> > sp_detach_db etc is an ok approach. There's a limit of 16 files with
that
> > approach, but you're no-where near that.
> >
> > There are also other options to do this online if needed, although they
> may
> > be slower & it sounds like you're probably ok with going offline anway.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "hector quintanilla" <hquintanilla@.cyberworksNOSPAM.com.mx> wrote in
> message
> > news:uCpbs9znDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > i am sorry for answering my own post, but i didn't see this part while
> > > reading BoL:
> > >
> > > If the database you are moving has additional data or log files,
specify
> > all
> > > of them in a comma-delimited list in the sp_attach_db stored
procedure.
> > The
> > > sp_detach_db procedure does not change no matter how many files the
> > database
> > > contains because it does not list them.
> > >
> > > thanks and sorry!
> > >
> > > "hector quintanilla" <hquintanilla@.cyberworksNOSPAM.com.mx> wrote in
> > message
> > > news:erashzznDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > > > hi all,
> > > >
> > > > could someone help me out on this question? i have a database made
up
> > of
> > > 3
> > > > .mdf files. the files are all on the same drive of a raid 10 so we
> need
> > > to
> > > > move them to different drives, as it is obvious that we are not
taking
> > > > advantage of the hardware. how do i move the mdf files? what is
the
> > best
> > > > possible way since the db is relativly big and the time frame we
have
> to
> > > > make this movement is small?
> > > >
> > > > thanks in advance.
> > > >
> > > > hector quintanilla
> > > >
> > > >
> > >
> > >
> >
> >
>