Relocate MSDE 2000 database?

[Sue Morton]

I'm not sure I'm posting to the right group... I hope you can help or point
me to another place to look.

I have a purchased application that installed and uses an MSDE 2000
database, no install choices were presented, it silently installed the
database to a subdirectory below C:\Program Files\Microsoft SQL Server
directory.

Is there a way I can relocate the database for this application, to another
disk? My C: partition is very small, I have ample room on other drives in
the system for the database to grow and prefer to keep all data out of the
OS partition if I can.

Also, I just installed another application, which appears to use SQL Server
2005 and also gave me no install choices, just silently installed to a
different subdirectory under C:\Program Files\Microsoft SQL Server. I would
like to relocate this one as well. (I realize there is another group for SQL
2005, just asking as long as I'm here.)

Thank you in advance for any help or for pointing me to further information
elsewhere. I have tried various searches on the 'net but so far have not
come up with something that fits this specific situation, where the database
was installed and managed by a 3rd party application.
--
Sue Morton

[William Vaughn [MVP]]

The server binaries can be uninstalled and reinstalled manually to relocate
the files using the setup wizard. This is a lot harder than moving the
database itself. You can use SQL Server Management Studio to detach the
database and log files and reattach them after having relocated to another
path.
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
[phone] (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

Post by Sue Morton
I'm not sure I'm posting to the right group... I hope you can help or
point me to another place to look.
I have a purchased application that installed and uses an MSDE 2000
database, no install choices were presented, it silently installed the
database to a subdirectory below C:\Program Files\Microsoft SQL Server
directory.
Is there a way I can relocate the database for this application, to
another disk? My C: partition is very small, I have ample room on other
drives in the system for the database to grow and prefer to keep all data
out of the OS partition if I can.
Also, I just installed another application, which appears to use SQL
Server 2005 and also gave me no install choices, just silently installed
to a different subdirectory under C:\Program Files\Microsoft SQL Server.
I would like to relocate this one as well. (I realize there is another
group for SQL 2005, just asking as long as I'm here.)
Thank you in advance for any help or for pointing me to further
information elsewhere. I have tried various searches on the 'net but so
far have not come up with something that fits this specific situation,
where the database was installed and managed by a 3rd party application.
--
Sue Morton

[Sue Morton]

Thanks for fast reply.

I am OK with leaving the executables where they are, just want to relocate
the databases.

I was trying this just now with OSQL from SQL 2005 installation, I relocated
the application database and logfile using OSQL. But I would really like to
relocate the entire 'data' subdirectory for that application. I am now
looking to find OSQL instructions for relocating tempdb, master, model, and
msdbdata files (.mdf and .ldf).

Can you point me to which SQL Server Management Studio version I should
download? I did several searches in MS download center for MSDE but don't
see anything that seems to apply to this situation. Except "SQL Server Web
Data Administrator" which looks like I have to install SQL Server 7
first...? It sounds like a management tool would be a lot simpler.

Thanks again for any help.
--
Sue Morton

Post by William Vaughn [MVP]
The server binaries can be uninstalled and reinstalled manually to
relocate the files using the setup wizard. This is a lot harder than
moving the database itself. You can use SQL Server Management Studio
to detach the database and log files and reattach them after having
relocated to another path.

Post by Sue Morton
I'm not sure I'm posting to the right group... I hope you can help or
point me to another place to look.
I have a purchased application that installed and uses an MSDE 2000
database, no install choices were presented, it silently installed
the database to a subdirectory below C:\Program Files\Microsoft SQL
Server directory.
Is there a way I can relocate the database for this application, to
another disk? My C: partition is very small, I have ample room on
other drives in the system for the database to grow and prefer to
keep all data out of the OS partition if I can.
Also, I just installed another application, which appears to use SQL
Server 2005 and also gave me no install choices, just silently
installed to a different subdirectory under C:\Program
Files\Microsoft SQL Server. I would like to relocate this one as
well. (I realize there is another group for SQL 2005, just asking as
long as I'm here.) Thank you in advance for any help or for pointing me
to further
information elsewhere. I have tried various searches on the 'net
but so far have not come up with something that fits this specific
situation, where the database was installed and managed by a 3rd
party application. --
Sue Morton

[Sue Morton]

Update: I found OSQL instructions for relocating TEMPDB and have moved it
successfully.

I am still searching for a way to relocate the system databases: Master,
Model, and MSDB... ?

Thanks again for any and all help or info.
--
Sue Morton

Post by Sue Morton
Thanks for fast reply.
I am OK with leaving the executables where they are, just want to
relocate the databases.
I was trying this just now with OSQL from SQL 2005 installation, I
relocated the application database and logfile using OSQL. But I
would really like to relocate the entire 'data' subdirectory for that
application. I am now looking to find OSQL instructions for
relocating tempdb, master, model, and msdbdata files (.mdf and .ldf).
Can you point me to which SQL Server Management Studio version I
should download? I did several searches in MS download center for
MSDE but don't see anything that seems to apply to this situation. Except
"SQL Server Web Data Administrator" which looks like I have to
install SQL Server 7 first...? It sounds like a management tool
would be a lot simpler.
Thanks again for any help.

Post by William Vaughn [MVP]
The server binaries can be uninstalled and reinstalled manually to
relocate the files using the setup wizard. This is a lot harder than
moving the database itself. You can use SQL Server Management Studio
to detach the database and log files and reattach them after having
relocated to another path.

Post by Sue Morton
I'm not sure I'm posting to the right group... I hope you can help
or point me to another place to look.
I have a purchased application that installed and uses an MSDE 2000
database, no install choices were presented, it silently installed
the database to a subdirectory below C:\Program Files\Microsoft SQL
Server directory.
Is there a way I can relocate the database for this application, to
another disk? My C: partition is very small, I have ample room on
other drives in the system for the database to grow and prefer to
keep all data out of the OS partition if I can.
Also, I just installed another application, which appears to use SQL
Server 2005 and also gave me no install choices, just silently
installed to a different subdirectory under C:\Program
Files\Microsoft SQL Server. I would like to relocate this one as
well. (I realize there is another group for SQL 2005, just asking as
long as I'm here.) Thank you in advance for any help or for
pointing me to further
information elsewhere. I have tried various searches on the 'net
but so far have not come up with something that fits this specific
situation, where the database was installed and managed by a 3rd
party application. --
Sue Morton

[Sue Morton]

Replying again to myself... :-)

Got the job done using OSQL (and SQLCMD with the other app that uses 2005).
All database files plus error logs moved to new drive. Application still
runs correct and is apparently none the wiser :-)

However, will still like to know if there is a Management utility I could
have used? Please point me if so.

Thank you,
--
Sue Morton

Post by Sue Morton
Update: I found OSQL instructions for relocating TEMPDB and have
moved it successfully.
Master, Model, and MSDB... ?
Thanks again for any and all help or info.

Post by Sue Morton
Thanks for fast reply.
I am OK with leaving the executables where they are, just want to
relocate the databases.
I was trying this just now with OSQL from SQL 2005 installation, I
relocated the application database and logfile using OSQL. But I
would really like to relocate the entire 'data' subdirectory for that
application. I am now looking to find OSQL instructions for
relocating tempdb, master, model, and msdbdata files (.mdf and
.ldf). Can you point me to which SQL Server Management Studio version I
should download? I did several searches in MS download center for
MSDE but don't see anything that seems to apply to this situation.
Except "SQL Server Web Data Administrator" which looks like I have to
install SQL Server 7 first...? It sounds like a management tool
would be a lot simpler.
Thanks again for any help.

Post by William Vaughn [MVP]
The server binaries can be uninstalled and reinstalled manually to
relocate the files using the setup wizard. This is a lot harder than
moving the database itself. You can use SQL Server Management Studio
to detach the database and log files and reattach them after having
relocated to another path.

Post by Sue Morton
I'm not sure I'm posting to the right group... I hope you can help
or point me to another place to look.
I have a purchased application that installed and uses an MSDE 2000
database, no install choices were presented, it silently installed
the database to a subdirectory below C:\Program Files\Microsoft SQL
Server directory.
Is there a way I can relocate the database for this application, to
another disk? My C: partition is very small, I have ample room on
other drives in the system for the database to grow and prefer to
keep all data out of the OS partition if I can.
Also, I just installed another application, which appears to use
SQL Server 2005 and also gave me no install choices, just silently
installed to a different subdirectory under C:\Program
Files\Microsoft SQL Server. I would like to relocate this one as
well. (I realize there is another group for SQL 2005, just asking
as long as I'm here.) Thank you in advance for any help or for
pointing me to further
information elsewhere. I have tried various searches on the 'net
but so far have not come up with something that fits this specific
situation, where the database was installed and managed by a 3rd
party application. --
Sue Morton

[William Vaughn [MVP]]

Sue, you're working with a SQL Server engine that's over 8 years old. SQL
Server Express 2005 has far more sophisticated tools.
Incidentally, if you're looking for a way to create a FAR lighter footprint
DBMS engine, I would consider SQL Server Compact. This operates through a
set of DLLs that can fit on a floppy and uses a single file that can be
almost anywhere.
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
[phone] (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

Post by Sue Morton
Replying again to myself... :-)
Got the job done using OSQL (and SQLCMD with the other app that uses
2005). All database files plus error logs moved to new drive. Application
still runs correct and is apparently none the wiser :-)
However, will still like to know if there is a Management utility I could
have used? Please point me if so.
Thank you,
--
Sue Morton

Post by Sue Morton
Update: I found OSQL instructions for relocating TEMPDB and have
moved it successfully.
Master, Model, and MSDB... ?
Thanks again for any and all help or info.

Post by Sue Morton
Thanks for fast reply.
I am OK with leaving the executables where they are, just want to
relocate the databases.
I was trying this just now with OSQL from SQL 2005 installation, I
relocated the application database and logfile using OSQL. But I
would really like to relocate the entire 'data' subdirectory for that
application. I am now looking to find OSQL instructions for
relocating tempdb, master, model, and msdbdata files (.mdf and
.ldf). Can you point me to which SQL Server Management Studio version I
should download? I did several searches in MS download center for
MSDE but don't see anything that seems to apply to this situation.
Except "SQL Server Web Data Administrator" which looks like I have to
install SQL Server 7 first...? It sounds like a management tool
would be a lot simpler.
Thanks again for any help.

Post by William Vaughn [MVP]
The server binaries can be uninstalled and reinstalled manually to
relocate the files using the setup wizard. This is a lot harder than
moving the database itself. You can use SQL Server Management Studio
to detach the database and log files and reattach them after having
relocated to another path.

Post by Sue Morton
I'm not sure I'm posting to the right group... I hope you can help
or point me to another place to look.
I have a purchased application that installed and uses an MSDE 2000
database, no install choices were presented, it silently installed
the database to a subdirectory below C:\Program Files\Microsoft SQL
Server directory.
Is there a way I can relocate the database for this application, to
another disk? My C: partition is very small, I have ample room on
other drives in the system for the database to grow and prefer to
keep all data out of the OS partition if I can.
Also, I just installed another application, which appears to use
SQL Server 2005 and also gave me no install choices, just silently
installed to a different subdirectory under C:\Program
Files\Microsoft SQL Server. I would like to relocate this one as
well. (I realize there is another group for SQL 2005, just asking
as long as I'm here.) Thank you in advance for any help or for
pointing me to further
information elsewhere. I have tried various searches on the 'net
but so far have not come up with something that fits this specific
situation, where the database was installed and managed by a 3rd
party application. --
Sue Morton

[Andrea Montanari]

hi Sue,

Post by Sue Morton
Can you point me to which SQL Server Management Studio version I
should download? I did several searches in MS download center for
MSDE but don't see anything that seems to apply to this situation. Except
"SQL Server Web Data Administrator" which looks like I have to
install SQL Server 7 first...? It sounds like a management tool
would be a lot simpler.

as Bill pointed out, you should upgrade that MSDE instance to SQLExpress as
soon as possible..
as regard a management tool for MSDE, SQL Server Management Studio Express
(http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=6053c6f8-82c8-479c-b25b-9aca13141c9e)
should solve your problems.. or you can have a look at a free prj of mine
available for free at the link following my sign., which implements a
management utility similar to the old Enterprise Manager for SQL Server
2000..

[Sue Morton]

I hear you but I don't know that I should do this? Recall my 1st post --
these databases are installed and deployed by 3rd party apps that I
purchased, I am not the developer.

Do you really think it is wise to upgrade these databases independent of the
vendor's support? If I were to upgrade the SQL server behind these apps,
I'm concerned I would not be able to reinstall the original and/or update
the software as provided by the vendor. By moving their databases after the
fact, at least I know I can back them up and even restore them to the
original location, and even if I have to reinstall the vendor's app from
scratch (e.g. disk failure, new computer etc.) I can still run the app.

Suggestions?
--
Sue Morton

Post by Andrea Montanari
hi Sue,

Post by Sue Morton
Can you point me to which SQL Server Management Studio version I
should download? I did several searches in MS download center for
MSDE but don't see anything that seems to apply to this situation.
Except "SQL Server Web Data Administrator" which looks like I have to
install SQL Server 7 first...? It sounds like a management tool
would be a lot simpler.

as Bill pointed out, you should upgrade that MSDE instance to
SQLExpress as soon as possible..
as regard a management tool for MSDE, SQL Server Management Studio Express
(http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=6053c6f8-82c8-479c-b25b-9aca13141c9e)
should solve your problems.. or you can have a look at a free prj of
mine available for free at the link following my sign., which
implements a management utility similar to the old Enterprise Manager
for SQL Server 2000..

[Andrea Montanari]

hi Sue,

Post by Sue Morton
I hear you but I don't know that I should do this? Recall my 1st
post -- these databases are installed and deployed by 3rd party apps
that I purchased, I am not the developer.
Do you really think it is wise to upgrade these databases independent
of the vendor's support? If I were to upgrade the SQL server behind
these apps, I'm concerned I would not be able to reinstall the
original and/or update the software as provided by the vendor. By
moving their databases after the fact, at least I know I can back
them up and even restore them to the original location, and even if I
have to reinstall the vendor's app from scratch (e.g. disk failure,
new computer etc.) I can still run the app.

ok, the reasons you are stuck with MSDE are clear and loud.. and, as long as
that data and relative application are critical to you as it seems, you can
only try to "force" via your "customer's economical power" the ISV to
support SQLExpress edition as well..
it's absolutely not wise to just "upgrade yourself" as you would for sure
lose support for it, and you probably pay for it.. but these are not
technical concerns but "legal ones" only...
there are "things" that could be broken in the migration from SQL Server
2000 code base to SQL Server 2005, and you, of course, have to be granted
they will not..
if you have spare time and want to, you could set up a test environment with
SQLExpress and give it a try, but of course this is not a certified test,
just a "poor's man try"...
best luck

[Sue Morton]

Hi Andrea,

I've already hammered on the vendors for not offering database placement as
part of the installation, and to use a more current database engine. But
then, I'm using XP Pro which is 10 years old (OK it wasn't yet a release in
1998, but still) so an 8 year old database solution doesn't seem that
far-fetched :-D

Back to William Vaughn's first post, a suggestion to use SQL Server
Management Studio, can you point me to one that will work with MSDE 2000 (or
MSDE 2005)? I looked through those available at MS Download Center and I'm
unclear which one would work?

I do have the OSQL (or SQLCMD in the case of 2005) scripts written now so it
is not a huge deal, but it would be nice to use a management tool if one is
available.

Thanks and regards,
--
Sue Morton

Post by Andrea Montanari
hi Sue,

Post by Sue Morton
I hear you but I don't know that I should do this? Recall my 1st
post -- these databases are installed and deployed by 3rd party apps
that I purchased, I am not the developer.
Do you really think it is wise to upgrade these databases independent
of the vendor's support? If I were to upgrade the SQL server behind
these apps, I'm concerned I would not be able to reinstall the
original and/or update the software as provided by the vendor. By
moving their databases after the fact, at least I know I can back
them up and even restore them to the original location, and even if I
have to reinstall the vendor's app from scratch (e.g. disk failure,
new computer etc.) I can still run the app.

ok, the reasons you are stuck with MSDE are clear and loud.. and, as
long as that data and relative application are critical to you as it
seems, you can only try to "force" via your "customer's economical
power" the ISV to support SQLExpress edition as well..
it's absolutely not wise to just "upgrade yourself" as you would for
sure lose support for it, and you probably pay for it.. but these are
not technical concerns but "legal ones" only...
there are "things" that could be broken in the migration from SQL
Server 2000 code base to SQL Server 2005, and you, of course, have to
be granted they will not..
if you have spare time and want to, you could set up a test
environment with SQLExpress and give it a try, but of course this is
not a certified test, just a "poor's man try"...
best luck

[Andrea Montanari]

hi Sue,

Post by Sue Morton
I've already hammered on the vendors for not offering database
placement as part of the installation, and to use a more current
database engine. But then, I'm using XP Pro which is 10 years old
(OK it wasn't yet a release in 1998, but still) so an 8 year old
database solution doesn't seem that far-fetched :-D

:(
go on hammering him :)

Post by Sue Morton
Back to William Vaughn's first post, a suggestion to use SQL Server
Management Studio, can you point me to one that will work with MSDE
2000 (or MSDE 2005)? I looked through those available at MS Download
Center and I'm unclear which one would work?
I do have the OSQL (or SQLCMD in the case of 2005) scripts written
now so it is not a huge deal, but it would be nice to use a
management tool if one is available.

again, you can find the free edition of SQL Server Management Studio Express
at
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=6053c6f8-82c8-479c-b25b-9aca13141c9e
..
I had sometime problem with it working with MSDE 2000, so, for that platform
I still use my free prj (you can get for free at
http://www.asql.biz/en/DbaMgr.aspx), DbaMgr2k, for it..

[William Vaughn [MVP]]

That's going to be the never-ending problem with MSDE. Most of the 2000
versions of SQL Server and thus MSDE have been pulled. Tool were always a
problem for MSDE which is why I recommended installing the Developer Edition
tools. It can be purchased for about $40 (about 2 Euros) and can manage any
version of SQL Server.

Again, this is a point you can bring up with your customer. It will be
increasingly expensive to support no-supported databases as time goes on.
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
[phone] (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

Post by Sue Morton
Hi Andrea,
I've already hammered on the vendors for not offering database placement
as part of the installation, and to use a more current database engine.
But then, I'm using XP Pro which is 10 years old (OK it wasn't yet a
release in 1998, but still) so an 8 year old database solution doesn't
seem that far-fetched :-D
Back to William Vaughn's first post, a suggestion to use SQL Server
Management Studio, can you point me to one that will work with MSDE 2000
(or MSDE 2005)? I looked through those available at MS Download Center
and I'm unclear which one would work?
I do have the OSQL (or SQLCMD in the case of 2005) scripts written now so
it is not a huge deal, but it would be nice to use a management tool if
one is available.
Thanks and regards,
--
Sue Morton

Post by Andrea Montanari
hi Sue,

Post by Sue Morton
I hear you but I don't know that I should do this? Recall my 1st
post -- these databases are installed and deployed by 3rd party apps
that I purchased, I am not the developer.
Do you really think it is wise to upgrade these databases independent
of the vendor's support? If I were to upgrade the SQL server behind
these apps, I'm concerned I would not be able to reinstall the
original and/or update the software as provided by the vendor. By
moving their databases after the fact, at least I know I can back
them up and even restore them to the original location, and even if I
have to reinstall the vendor's app from scratch (e.g. disk failure,
new computer etc.) I can still run the app.

ok, the reasons you are stuck with MSDE are clear and loud.. and, as
long as that data and relative application are critical to you as it
seems, you can only try to "force" via your "customer's economical
power" the ISV to support SQLExpress edition as well..
it's absolutely not wise to just "upgrade yourself" as you would for
sure lose support for it, and you probably pay for it.. but these are
not technical concerns but "legal ones" only...
there are "things" that could be broken in the migration from SQL
Server 2000 code base to SQL Server 2005, and you, of course, have to
be granted they will not..
if you have spare time and want to, you could set up a test
environment with SQLExpress and give it a try, but of course this is
not a certified test, just a "poor's man try"...
best luck