Moving an SQL Server 2000 database

When I crated my first SQL Server 2000 database I overlooked the database
lcoation on the harddisk - which by default points to the C: drive. I need
to move it all over to the d: drive and place in the appropriate directory.
What is the best way to accomplish this? Can I use DTS for this or is there
a better way to do this?
Once moved, is there any thing else that I need to do manually to make sure
that everything related to the database did get moved propery and all the
privs are set etc... etc..
Thanks, Brad
If it is going to remain on the same SQL Instance then you have two very
easy options.
1. Detach the files, move them and reattach them
2. Restore a full backup using the WITH MOVE option to change the file
locations.
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
Andrew J. Kelly SQL MVP
"Brad Pears" <bradp@.truenorthloghomes.com> wrote in message
news:eCiaDuosHHA.4916@.TK2MSFTNGP04.phx.gbl...
> When I crated my first SQL Server 2000 database I overlooked the database
> lcoation on the harddisk - which by default points to the C: drive. I need
> to move it all over to the d: drive and place in the appropriate
> directory.
> What is the best way to accomplish this? Can I use DTS for this or is
> there a better way to do this?
> Once moved, is there any thing else that I need to do manually to make
> sure that everything related to the database did get moved propery and all
> the privs are set etc... etc..
> Thanks, Brad
>
|||Brad Pears (bradp@.truenorthloghomes.com) writes:
> When I crated my first SQL Server 2000 database I overlooked the
> database lcoation on the harddisk - which by default points to the C:
> drive. I need to move it all over to the d: drive and place in the
> appropriate directory.
> What is the best way to accomplish this? Can I use DTS for this or is
> there a better way to do this?
Definitely. Run sp_detach_db, use Explorer to move the file to the desired
location, and then use sp_attach_db to make SQL Server aware of the new
location. (When you detach it, the database is gone as far as SQL Server
is concerned).
See Books Online for exact details on parameters etc.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks for the comments. I used the detach / attach and it worked like a
top. Although I did hear that using the detach/attach option can sometimes
corrup the DB? Have either of you ever had this problem?
Thanks, Brad
"Brad Pears" <bradp@.truenorthloghomes.com> wrote in message
news:eCiaDuosHHA.4916@.TK2MSFTNGP04.phx.gbl...
> When I crated my first SQL Server 2000 database I overlooked the database
> lcoation on the harddisk - which by default points to the C: drive. I need
> to move it all over to the d: drive and place in the appropriate
> directory.
> What is the best way to accomplish this? Can I use DTS for this or is
> there a better way to do this?
> Once moved, is there any thing else that I need to do manually to make
> sure that everything related to the database did get moved propery and all
> the privs are set etc... etc..
> Thanks, Brad
>
|||Not if you do it correctly. If you simply copy over the files without first
detaching them you can have issues.
Andrew J. Kelly SQL MVP
"Brad Pears" <bradp@.truenorthloghomes.com> wrote in message
news:O36bt81sHHA.3504@.TK2MSFTNGP05.phx.gbl...
> Thanks for the comments. I used the detach / attach and it worked like a
> top. Although I did hear that using the detach/attach option can sometimes
> corrup the DB? Have either of you ever had this problem?
> Thanks, Brad
> "Brad Pears" <bradp@.truenorthloghomes.com> wrote in message
> news:eCiaDuosHHA.4916@.TK2MSFTNGP04.phx.gbl...
>

Move sql database to diverend harddisk with same drive letter

I have a small d: drive and like to change this with a bigger one, how can i
move the database from old drive to new drive and use te same drive letter
after the database move? i installed the new harddrive with letter e: Can i
stop the sql server and copy the whole drive d: to drive e: and than change
driveletter to d:?
ThanksYou can move the data file. you would need to deatach the database, move the
file phisicaly to another drive (mdf or ndf and ldf) then atach the database
and it will ask you for a location of the new data files (both mdf/ndf and
ldf). point to the newly created drive and new location of your files and
that is it.
Do me a favor, it has been 2 years since I did it; google it "move data
files to another drive" and you will get tons of hits. This is non system DB
?
This is one of your own databases?
"Luq" wrote:

> I have a small d: drive and like to change this with a bigger one, how can
i
> move the database from old drive to new drive and use te same drive letter
> after the database move? i installed the new harddrive with letter e: Can
i
> stop the sql server and copy the whole drive d: to drive e: and than chang
e
> driveletter to d:?
> Thanks|||Her you go Luq
http://support.microsoft.com/kb/224071
"Luq" wrote:

> I have a small d: drive and like to change this with a bigger one, how can
i
> move the database from old drive to new drive and use te same drive letter
> after the database move? i installed the new harddrive with letter e: Can
i
> stop the sql server and copy the whole drive d: to drive e: and than chang
e
> driveletter to d:?
> Thanks

Move sql database to diverend harddisk with same drive letter

I have a small d: drive and like to change this with a bigger one, how can i
move the database from old drive to new drive and use te same drive letter
after the database move? i installed the new harddrive with letter e: Can i
stop the sql server and copy the whole drive d: to drive e: and than change
driveletter to d:?
ThanksYou can move the data file. you would need to deatach the database, move the
file phisicaly to another drive (mdf or ndf and ldf) then atach the database
and it will ask you for a location of the new data files (both mdf/ndf and
ldf). point to the newly created drive and new location of your files and
that is it.
Do me a favor, it has been 2 years since I did it; google it "move data
files to another drive" and you will get tons of hits. This is non system DB?
This is one of your own databases?
"Luq" wrote:
> I have a small d: drive and like to change this with a bigger one, how can i
> move the database from old drive to new drive and use te same drive letter
> after the database move? i installed the new harddrive with letter e: Can i
> stop the sql server and copy the whole drive d: to drive e: and than change
> driveletter to d:?
> Thanks|||Her you go Luq
http://support.microsoft.com/kb/224071
"Luq" wrote:
> I have a small d: drive and like to change this with a bigger one, how can i
> move the database from old drive to new drive and use te same drive letter
> after the database move? i installed the new harddrive with letter e: Can i
> stop the sql server and copy the whole drive d: to drive e: and than change
> driveletter to d:?
> Thanks