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 Tables From Local to Hosting Provider's Server

I know about BOL. Please take a moment to refer me to any tutorials that may
help me to learn the basics as well as the finer points of moving tables and
other assets to the hosting provider's server.
<%= Clinton Gallagher
METROmilwaukee (sm) "A Regional Information Service"
NET csgallagher AT metromilwaukee.com
URL http://metromilwaukee.com/
URL http://clintongallagher.metromilwaukee.com/
Due to the limitations for access to the providers SQl Servers I use QALite
to script data from my tables and connect to the remote database via QA and
execute the scripts, thats the best way for me.
HTH, Jens Smeyer.
"clintonG" <csgallagher@.REMOVETHISTEXTmetromilwaukee.com> wrote in message
news:OQjsao$qFHA.1788@.tk2msftngp13.phx.gbl...
>I know about BOL. Please take a moment to refer me to any tutorials that
>may help me to learn the basics as well as the finer points of moving
>tables and other assets to the hosting provider's server.
> <%= Clinton Gallagher
> METROmilwaukee (sm) "A Regional Information Service"
> NET csgallagher AT metromilwaukee.com
> URL http://metromilwaukee.com/
> URL http://clintongallagher.metromilwaukee.com/
>
|||Thanks for your comments Jens.
I can evaluate the same tool you are using but I can connect to my database
on the hosted site using Enterprise Manager or a tool with a web interface
the hosting provider makes available.
<%= Clinton Gallagher
"Jens Smeyer" <Jens@.remove_this_for_contacting_sqlserver2005.de> wrote in
message news:%23dFQ7WArFHA.1788@.tk2msftngp13.phx.gbl...
> Due to the limitations for access to the providers SQl Servers I use
> QALite to script data from my tables and connect to the remote database
> via QA and execute the scripts, thats the best way for me.
> HTH, Jens Smeyer.
>
> "clintonG" <csgallagher@.REMOVETHISTEXTmetromilwaukee.com> wrote in message
> news:OQjsao$qFHA.1788@.tk2msftngp13.phx.gbl...
>
|||To be honest, to me these tools really suck. The WebTools provided by the
providers often don′t offer all the capabilities that I need, so I used to
rely on the standard tools like EM and QA, they are the best.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"clintonG" wrote:

> Thanks for your comments Jens.
> I can evaluate the same tool you are using but I can connect to my database
> on the hosted site using Enterprise Manager or a tool with a web interface
> the hosting provider makes available.
> <%= Clinton Gallagher
> "Jens Sü?meyer" <Jens@.remove_this_for_contacting_sqlserver2005.de> wrote in
> message news:%23dFQ7WArFHA.1788@.tk2msftngp13.phx.gbl...
>
>
|||Is this [1] what you're refrring to? Why would I not just use Enterprise
Manager?
I agree the tools with web interface can be less capable than desktop tools
but -- at the moment -- I don't even have a clue what tasks I need to
consider to move the tables to the hosting provider server. Its not like FTP
where I can just upload files I guess is it? :-( and its not like I don't
have the interest or the skills to learn but I need to learn what to do
before I decide what tools are best to do it that's why I've been asking for
referrals to tutorials.
<%= Clinton Gallagher
[1] http://www.rac4sql.net/qalite_main.asp
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:E198A5D7-46A2-489C-BB03-E8E7ECF18625@.microsoft.com...[vbcol=seagreen]
> To be honest, to me these tools really suck. The WebTools provided by the
> providers often dont offer all the capabilities that I need, so I used to
> rely on the standard tools like EM and QA, they are the best.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "clintonG" wrote:

Move Tables From Local to Hosting Provider's Server

I know about BOL. Please take a moment to refer me to any tutorials that may
help me to learn the basics as well as the finer points of moving tables and
other assets to the hosting provider's server.
<%= Clinton Gallagher
METROmilwaukee (sm) "A Regional Information Service"
NET csgallagher AT metromilwaukee.com
URL http://metromilwaukee.com/
URL http://clintongallagher.metromilwaukee.com/Due to the limitations for access to the providers SQl Servers I use QALite
to script data from my tables and connect to the remote database via QA and
execute the scripts, thats the best way for me.
HTH, Jens Smeyer.
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:OQjsao$qFHA.1788@.tk2msftngp13.phx.gbl...
>I know about BOL. Please take a moment to refer me to any tutorials that
>may help me to learn the basics as well as the finer points of moving
>tables and other assets to the hosting provider's server.
> <%= Clinton Gallagher
> METROmilwaukee (sm) "A Regional Information Service"
> NET csgallagher AT metromilwaukee.com
> URL http://metromilwaukee.com/
> URL http://clintongallagher.metromilwaukee.com/
>|||Thanks for your comments Jens.
I can evaluate the same tool you are using but I can connect to my database
on the hosted site using Enterprise Manager or a tool with a web interface
the hosting provider makes available.
<%= Clinton Gallagher
"Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote in
message news:%23dFQ7WArFHA.1788@.tk2msftngp13.phx.gbl...
> Due to the limitations for access to the providers SQl Servers I use
> QALite to script data from my tables and connect to the remote database
> via QA and execute the scripts, thats the best way for me.
> HTH, Jens Smeyer.
>
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:OQjsao$qFHA.1788@.tk2msftngp13.phx.gbl...
>|||To be honest, to me these tools really suck. The WebTools provided by the
providers often don′t offer all the capabilities that I need, so I used to
rely on the standard tools like EM and QA, they are the best.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"clintonG" wrote:

> Thanks for your comments Jens.
> I can evaluate the same tool you are using but I can connect to my databas
e
> on the hosted site using Enterprise Manager or a tool with a web interface
> the hosting provider makes available.
> <%= Clinton Gallagher
> "Jens Sü?meyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote
in
> message news:%23dFQ7WArFHA.1788@.tk2msftngp13.phx.gbl...
>
>|||Is this [1] what you're refrring to? Why would I not just use Enterprise
Manager?
I agree the tools with web interface can be less capable than desktop tools
but -- at the moment -- I don't even have a clue what tasks I need to
consider to move the tables to the hosting provider server. Its not like FTP
where I can just upload files I guess is it? :-( and its not like I don't
have the interest or the skills to learn but I need to learn what to do
before I decide what tools are best to do it that's why I've been asking for
referrals to tutorials.
<%= Clinton Gallagher
[1] http://www.rac4sql.net/qalite_main.asp
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.
de>
wrote in message news:E198A5D7-46A2-489C-BB03-E8E7ECF18625@.microsoft.com...[vbcol=seagreen]
> To be honest, to me these tools really suck. The WebTools provided by the
> providers often dont offer all the capabilities that I need, so I used to
> rely on the standard tools like EM and QA, they are the best.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "clintonG" wrote:
>sql

Move Tables From Local to Hosting Provider's Server

I know about BOL. Please take a moment to refer me to any tutorials that may
help me to learn the basics as well as the finer points of moving tables and
other assets to the hosting provider's server.
<%= Clinton Gallagher
METROmilwaukee (sm) "A Regional Information Service"
NET csgallagher AT metromilwaukee.com
URL http://metromilwaukee.com/
URL http://clintongallagher.metromilwaukee.com/Due to the limitations for access to the providers SQl Servers I use QALite
to script data from my tables and connect to the remote database via QA and
execute the scripts, thats the best way for me.
HTH, Jens Süßmeyer.
"clintonG" <csgallagher@.REMOVETHISTEXTmetromilwaukee.com> wrote in message
news:OQjsao$qFHA.1788@.tk2msftngp13.phx.gbl...
>I know about BOL. Please take a moment to refer me to any tutorials that
>may help me to learn the basics as well as the finer points of moving
>tables and other assets to the hosting provider's server.
> <%= Clinton Gallagher
> METROmilwaukee (sm) "A Regional Information Service"
> NET csgallagher AT metromilwaukee.com
> URL http://metromilwaukee.com/
> URL http://clintongallagher.metromilwaukee.com/
>|||Thanks for your comments Jens.
I can evaluate the same tool you are using but I can connect to my database
on the hosted site using Enterprise Manager or a tool with a web interface
the hosting provider makes available.
<%= Clinton Gallagher
"Jens Süßmeyer" <Jens@.remove_this_for_contacting_sqlserver2005.de> wrote in
message news:%23dFQ7WArFHA.1788@.tk2msftngp13.phx.gbl...
> Due to the limitations for access to the providers SQl Servers I use
> QALite to script data from my tables and connect to the remote database
> via QA and execute the scripts, thats the best way for me.
> HTH, Jens Süßmeyer.
>
> "clintonG" <csgallagher@.REMOVETHISTEXTmetromilwaukee.com> wrote in message
> news:OQjsao$qFHA.1788@.tk2msftngp13.phx.gbl...
>>I know about BOL. Please take a moment to refer me to any tutorials that
>>may help me to learn the basics as well as the finer points of moving
>>tables and other assets to the hosting provider's server.
>> <%= Clinton Gallagher
>> METROmilwaukee (sm) "A Regional Information Service"
>> NET csgallagher AT metromilwaukee.com
>> URL http://metromilwaukee.com/
>> URL http://clintongallagher.metromilwaukee.com/
>>
>|||To be honest, to me these tools really suck. The WebTools provided by the
providers often don´t offer all the capabilities that I need, so I used to
rely on the standard tools like EM and QA, they are the best.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"clintonG" wrote:
> Thanks for your comments Jens.
> I can evaluate the same tool you are using but I can connect to my database
> on the hosted site using Enterprise Manager or a tool with a web interface
> the hosting provider makes available.
> <%= Clinton Gallagher
> "Jens Sü�meyer" <Jens@.remove_this_for_contacting_sqlserver2005.de> wrote in
> message news:%23dFQ7WArFHA.1788@.tk2msftngp13.phx.gbl...
> > Due to the limitations for access to the providers SQl Servers I use
> > QALite to script data from my tables and connect to the remote database
> > via QA and execute the scripts, thats the best way for me.
> >
> > HTH, Jens Sü�meyer.
> >
> >
> > "clintonG" <csgallagher@.REMOVETHISTEXTmetromilwaukee.com> wrote in message
> > news:OQjsao$qFHA.1788@.tk2msftngp13.phx.gbl...
> >>I know about BOL. Please take a moment to refer me to any tutorials that
> >>may help me to learn the basics as well as the finer points of moving
> >>tables and other assets to the hosting provider's server.
> >>
> >> <%= Clinton Gallagher
> >> METROmilwaukee (sm) "A Regional Information Service"
> >> NET csgallagher AT metromilwaukee.com
> >> URL http://metromilwaukee.com/
> >> URL http://clintongallagher.metromilwaukee.com/
> >>
> >>
> >
> >
>
>|||Is this [1] what you're refrring to? Why would I not just use Enterprise
Manager?
I agree the tools with web interface can be less capable than desktop tools
but -- at the moment -- I don't even have a clue what tasks I need to
consider to move the tables to the hosting provider server. Its not like FTP
where I can just upload files I guess is it? :-( and its not like I don't
have the interest or the skills to learn but I need to learn what to do
before I decide what tools are best to do it that's why I've been asking for
referrals to tutorials.
<%= Clinton Gallagher
[1] http://www.rac4sql.net/qalite_main.asp
"Jens Süßmeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:E198A5D7-46A2-489C-BB03-E8E7ECF18625@.microsoft.com...
> To be honest, to me these tools really suck. The WebTools provided by the
> providers often don´t offer all the capabilities that I need, so I used to
> rely on the standard tools like EM and QA, they are the best.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "clintonG" wrote:
>> Thanks for your comments Jens.
>> I can evaluate the same tool you are using but I can connect to my
>> database
>> on the hosted site using Enterprise Manager or a tool with a web
>> interface
>> the hosting provider makes available.
>> <%= Clinton Gallagher
>> "Jens Süßmeyer" <Jens@.remove_this_for_contacting_sqlserver2005.de> wrote
>> in
>> message news:%23dFQ7WArFHA.1788@.tk2msftngp13.phx.gbl...
>> > Due to the limitations for access to the providers SQl Servers I use
>> > QALite to script data from my tables and connect to the remote database
>> > via QA and execute the scripts, thats the best way for me.
>> >
>> > HTH, Jens Süßmeyer.
>> >
>> >
>> > "clintonG" <csgallagher@.REMOVETHISTEXTmetromilwaukee.com> wrote in
>> > message
>> > news:OQjsao$qFHA.1788@.tk2msftngp13.phx.gbl...
>> >>I know about BOL. Please take a moment to refer me to any tutorials
>> >>that
>> >>may help me to learn the basics as well as the finer points of moving
>> >>tables and other assets to the hosting provider's server.
>> >>
>> >> <%= Clinton Gallagher
>> >> METROmilwaukee (sm) "A Regional Information Service"
>> >> NET csgallagher AT metromilwaukee.com
>> >> URL http://metromilwaukee.com/
>> >> URL http://clintongallagher.metromilwaukee.com/
>> >>
>> >>
>> >
>> >
>>