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