Change location of an Existing DB in MSDE

Hi everyone,
I have an MSDE database that was created using the osql.exe. By default it gets automatically created in the MSSQL7/data directory.
Does anyone know how i can detach and then re-attach the database from a new location?Refer to SP_ATTACH_DB & SP_DETACH_DB topics in books online.

Also you can mention path for data files while using CREATE DATABASE Statement.|||Have U thought of

1)Backup db
2)copy the mdf & ldf files
3)delete the db
4)create a new db with same name but data files in the new place
5)overwite the new files (s'be same name) with the original files
(Don't know if u have to stop the SQL Service to do this bit)

or do 1) 3) & 4) + restore db

Worth a try

Ye Hah !

GW

change instance name?

Is it easy to rename an instance, or change an instance to default instance?
Basically I have an SQL 2000 Standard and MSDE installed on same machine,
and I would like to swap around their instances.
Thanks
cj
No, you cannot rename an instance or change default to named or vice versa. Backup, (re)install and
restore is what you have to do. some links that might be useful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scri...p?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Is it easy to rename an instance, or change an instance to default instance?
> Basically I have an SQL 2000 Standard and MSDE installed on same machine,
> and I would like to swap around their instances.
> Thanks
> cj
>
>
>
|||AFAIK, you can not change instance name, you have to install new
instance and dettach dbs from old instance and attach them to new
instance.
Also you can not change named instance to default instance.
look at
http://vyaskn.tripod.com/administration_faq.htm#q13
Regards
Amish Shah.
|||Ok, that's what I thought :-(
BTW thanks for the list, it is a helpful resource!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oh3zzoJKGHA.500@.TK2MSFTNGP15.phx.gbl...
> No, you cannot rename an instance or change default to named or vice
> versa. Backup, (re)install and restore is what you have to do. some links
> that might be useful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
> news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
>

change instance name?

Is it easy to rename an instance, or change an instance to default instance?
Basically I have an SQL 2000 Standard and MSDE installed on same machine,
and I would like to swap around their instances.
Thanks
cjNo, you cannot rename an instance or change default to named or vice versa.
Backup, (re)install and
restore is what you have to do. some links that might be useful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scr...sp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Is it easy to rename an instance, or change an instance to default instanc
e?
> Basically I have an SQL 2000 Standard and MSDE installed on same machine,
> and I would like to swap around their instances.
> Thanks
> cj
>
>
>|||AFAIK, you can not change instance name, you have to install new
instance and dettach dbs from old instance and attach them to new
instance.
Also you can not change named instance to default instance.
look at
http://vyaskn.tripod.com/administration_faq.htm#q13
Regards
Amish Shah.|||Ok, that's what I thought :-(
BTW thanks for the list, it is a helpful resource!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oh3zzoJKGHA.500@.TK2MSFTNGP15.phx.gbl...
> No, you cannot rename an instance or change default to named or vice
> versa. Backup, (re)install and restore is what you have to do. some links
> that might be useful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
> news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
>

change instance name?

Is it easy to rename an instance, or change an instance to default instance?
Basically I have an SQL 2000 Standard and MSDE installed on same machine,
and I would like to swap around their instances.
Thanks
cjNo, you cannot rename an instance or change default to named or vice versa. Backup, (re)install and
restore is what you have to do. some links that might be useful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Is it easy to rename an instance, or change an instance to default instance?
> Basically I have an SQL 2000 Standard and MSDE installed on same machine,
> and I would like to swap around their instances.
> Thanks
> cj
>
>
>|||AFAIK, you can not change instance name, you have to install new
instance and dettach dbs from old instance and attach them to new
instance.
Also you can not change named instance to default instance.
look at
http://vyaskn.tripod.com/administration_faq.htm#q13
Regards
Amish Shah.|||Ok, that's what I thought :-(
BTW thanks for the list, it is a helpful resource!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oh3zzoJKGHA.500@.TK2MSFTNGP15.phx.gbl...
> No, you cannot rename an instance or change default to named or vice
> versa. Backup, (re)install and restore is what you have to do. some links
> that might be useful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
> news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
>> Is it easy to rename an instance, or change an instance to default
>> instance?
>> Basically I have an SQL 2000 Standard and MSDE installed on same machine,
>> and I would like to swap around their instances.
>> Thanks
>> cj
>>
>>
>

Change default to named instance?

I have an app that connects to the default instance of msde. After some
conflicts with other app's msdes, I now want to use a named instance. This
is no problem from within the app, but can I change the default msde
instance to a named instance without losing the any data, and without
reinstalling msde?
Hi,
No you cant do directly.
1. Take a backup of all databases (Including system databases)
2. Stop SQL Server and copy the MDF and LDF to a safe place
3. Un Install the MSDE
4. Install MSDE and same service pack as old with same folder structure
5. Stop sql server
6. COpy the MDF and LDF (taken in step 2) to the same folder as old
7. Restart SQL server service
8. Execute the below comamnds in OSQL to change the server name
sp_dropserver <oldserver>
go
sp_addserver <new named server>,local
Note:
If these steps fail (Step 6 and 7) then use the database backup taken in
step-1 to restore all the databases.
Thanks
Hari
MCDBA
"Robbs" <rms14can@.hotmail.com> wrote in message
news:e1kT4FGOEHA.3312@.tk2msftngp13.phx.gbl...
> I have an app that connects to the default instance of msde. After some
> conflicts with other app's msdes, I now want to use a named instance. This
> is no problem from within the app, but can I change the default msde
> instance to a named instance without losing the any data, and without
> reinstalling msde?
>
|||Hi Robbs,
You can not change a default installation to a named instance.
You will have to reinstall MSDE as a named instance.
You will not loose data. Just make sure that you backup the database.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks everyone!! Thats what I figured I would have to do; just wnated to
make sure there was no easier way.
Thnx Robb
"Ashish Ruparel [MSFT]" <v-ashrup@.online.microsoft.com> wrote in message
news:9k5UMmJOEHA.2692@.cpmsftngxa10.phx.gbl...
> Hi Robbs,
> You can not change a default installation to a named instance.
> You will have to reinstall MSDE as a named instance.
> You will not loose data. Just make sure that you backup the database.
> HTH
> Ashish
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

Change connection information Access 2003 with MSDE

I have a copy of an ADP and MSDE database that I tweak occasionally from an
alternate location. I used to be able to do this just fine. When I would
try to open the ADP the connection was broken because the server on this
computer I'm using has a different name than the "live" server.
Since I upgraded to Access 2003, when I open the ADP and try to edith the
ADP, I can't get to it. How can I change the connnection properties?
Linda
hi Linda
Linda Burnside wrote:
> I have a copy of an ADP and MSDE database that I tweak occasionally
> from an alternate location. I used to be able to do this just fine. When
> I would try to open the ADP the connection was broken because
> the server on this computer I'm using has a different name than the
> "live" server.
> Since I upgraded to Access 2003, when I open the ADP and try to edith
> the ADP, I can't get to it. How can I change the connnection
> properties?
in the File menu, you can access the "connection" menu item, which will
open the Data link dialog you can use to select the desired SQL Server
instance (and database)..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Yes, I know where it is. Problem is that nothing happens when I click on
Connection. It won't open.
Linda
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m7637F156p68U1@.individual.net...
> hi Linda
> Linda Burnside wrote:
> in the File menu, you can access the "connection" menu item, which will
> open the Data link dialog you can use to select the desired SQL Server
> instance (and database)..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Linda,
Linda Burnside wrote:
> Yes, I know where it is. Problem is that nothing happens when I
> click on Connection. It won't open.
does the Data Link dialog not open?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||The box that shows the connection information (if that's what you're calling
the data link dialogue) does not open. When I click on the word
"Connection" from the menu, nothing happens.
Linda
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m8l81F15g1kqU1@.individual.net...
> hi Linda,
> Linda Burnside wrote:
> does the Data Link dialog not open?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Since "Data Link Properties" dialog box is an independent COM component
comming with Windows, you can test it with a "*.udl" file to make sure the
"Data Link Properties" dialog box itself is OK or not. If it is OK for
"*.udl" file, then the problen could be the installation of Access2003. If
the "Data Link Properties" dialog box does not work even with "*.udl", you
may have to repair your Windows or Windows registry (I am not surewhat to do
or what the cause is).
To test "Data Link Properties" dialog box:
1. Open Notepad;
2. Save a blank Notepad file on desktop as "MyDataLink.udl";
3. Double-click "MyDataLink.udl", which should open "Data Link Properties"
dialog box.
"Linda Burnside" <linda@.nospam_burnsidebiz.com> wrote in message
news:N0ILe.52$Z%6.1@.newssvr17.news.prodigy.com...
> The box that shows the connection information (if that's what you're
calling
> the data link dialogue) does not open. When I click on the word
> "Connection" from the menu, nothing happens.
> Linda
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3m8l81F15g1kqU1@.individual.net...
>

Celeron Processor

All the documentation says that a Celeron processor will not run MSDE 2000.
Just loaded a small enterprise app that is based on MSDE2000. Now the PC in
question is just out of the box Celeron PC (1.5G M370). Now would this just
be a freak occurance that does not portend fo the the futre?
hi,
CWells wrote:
> All the documentation says that a Celeron processor will not run MSDE
> 2000. Just loaded a small enterprise app that is based on MSDE2000.
> Now the PC in question is just out of the box Celeron PC (1.5G M370).
> Now would this just be a freak occurance that does not portend fo the
> the futre?
AFAIK, there's not such a constraint for MSDE not running on celeron
processors... (http://www.google.it/search?hl=it&q=...+celeron&meta=)
can you please post a link about that?
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply