Correct way of unistalling MSDE instance

Hi,
I am installing an MSDE instance using Setup ... call using a bat file.
I know the name of the instance (its a Given).
How do I uninstall automatically using a similar call?
I cannot afford to ask the user to uninstall it manually by using 'Add
remove programs'.
Also I would like to know if what I want to do is the correct way
or
Is there a standard way to uninstall (but not manually)?
In anycase I need to know how to uninstall an MSDE instance (Not MANUALLY) ?
Thanks
Subhojit
You can follow the following steps to uninstall manually.
Remove Files and Folders
Remove the MSDE 2000 instance data and program installation folders. You
can find the root folder information for the default instance data folder
in the SQLDataRoot registry key value under this registry key path:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
For example, remove the MSDE 2000 data folder for a default instance:
\Program Files\Microsoft SQL Server\MSSQL\Data
For example, remove the MSDE 2000 data folder for a named instance:
\Program Files\Microsoft SQL Server\MSSQL$<INSTANCENAME>\Data
For example, remove the MSDE 2000 program folder for a default instance:
\Program Files\Microsoft SQL Server\MSSQL\Binn
For example, remove the MSDE 2000 program folder for a named instance:
\Program Files\Microsoft SQL Server\MSSQL$<INSTANCENAME>\Binn
back to the top
Clean Up the Registry
WARNING : If you use Registry Editor incorrectly, you may cause serious
problems that may require you to reinstall your operating system. Microsoft
cannot guarantee that you can solve problems that result from using
Registry Editor incorrectly. Use Registry Editor at your own risk.
The Msizap.exe tool removes only Windows Installer specific keys or data
for the ProductCode . It is best to manually remove the MSDE 2000 registry
keys. Use Registry Editor to remove the following MSDE 2000 registry keys:
For an MSDE 2000 default instance, remove the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
For an MSDE 2000 named instance, remove the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<INSTANCENAME>
If the following registry key points to the MSDE 2000 instance ProductCode
, remove the value InstanceComponentSet.x . For example,
InstanceComponentSet.1 has a value that matches the ProductCode of
Sqlrun01.msi:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Component
Set\InstanceComponentSet.1
Remove the SQLServer Service registry key.
For an MSDE 2000 default instance, remove the following:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\MSSQLServer
For an MSDE 2000 named instance, remove the following:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\MSSQL$<INSTANCENAME>
Remove the SQLServerAgent Service registry key:
For an MSDE 2000 default instance, remove the following:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\SQLServerAgent
For an MSDE 2000 named instance, remove the following:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\SQLAgent$<INSTANCENAME>
Girish Sundaram
Microsoft SQL Server Support Engineer
E-mail: girishs@.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Girish,
Thanks for replying.
**** "Just a request. Please read the posting before replying." ****
I really need to know how to uninstall MSDE instance "programatically" and
"not Manually".
Regards
"Girish Sundaram" wrote:

> You can follow the following steps to uninstall manually.
> Remove Files and Folders
> Remove the MSDE 2000 instance data and program installation folders. You
> can find the root folder information for the default instance data folder
> in the SQLDataRoot registry key value under this registry key path:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
> For example, remove the MSDE 2000 data folder for a default instance:
> \Program Files\Microsoft SQL Server\MSSQL\Data
> For example, remove the MSDE 2000 data folder for a named instance:
> \Program Files\Microsoft SQL Server\MSSQL$<INSTANCENAME>\Data
> For example, remove the MSDE 2000 program folder for a default instance:
> \Program Files\Microsoft SQL Server\MSSQL\Binn
> For example, remove the MSDE 2000 program folder for a named instance:
> \Program Files\Microsoft SQL Server\MSSQL$<INSTANCENAME>\Binn
> back to the top
> Clean Up the Registry
> WARNING : If you use Registry Editor incorrectly, you may cause serious
> problems that may require you to reinstall your operating system. Microsoft
> cannot guarantee that you can solve problems that result from using
> Registry Editor incorrectly. Use Registry Editor at your own risk.
> The Msizap.exe tool removes only Windows Installer specific keys or data
> for the ProductCode . It is best to manually remove the MSDE 2000 registry
> keys. Use Registry Editor to remove the following MSDE 2000 registry keys:
> For an MSDE 2000 default instance, remove the following key:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
> For an MSDE 2000 named instance, remove the following key:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<INSTANCENAME>
> If the following registry key points to the MSDE 2000 instance ProductCode
> , remove the value InstanceComponentSet.x . For example,
> InstanceComponentSet.1 has a value that matches the ProductCode of
> Sqlrun01.msi:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Component
> Set\InstanceComponentSet.1
>
> Remove the SQLServer Service registry key.
> For an MSDE 2000 default instance, remove the following:
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\MSSQLServer
> For an MSDE 2000 named instance, remove the following:
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\MSSQL$<INSTANCENAME>
>
> Remove the SQLServerAgent Service registry key:
> For an MSDE 2000 default instance, remove the following:
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\SQLServerAgent
> For an MSDE 2000 named instance, remove the following:
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\SQLAgent$<INSTANCENAME>
>
> Girish Sundaram
> Microsoft SQL Server Support Engineer
> E-mail: girishs@.microsoft.com
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Possibly you can use a batch file to delete the folders from the drives.
You can use a script to delete the registry entries. The command to do so
is shown below.
The command that you can use to remove registry entries is
xp_deletevalue
Syntax:
xp_deletevalue hive, key, value
Example:
EXEC master..xp_regdeletevalue 'HKEY_LOCAL_MACHINE',
'Software\Clients', 'PaulWehland'
Hope this answers your query
Girish Sundaram
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi -
As you probably know, you can manually remove an MSDE instance using the
add/remove programs in the Control Panel. While that doesn't 'completely'
remove MSDE, you can do the same thing programmatically:
1. Read the MSDE GUID value from the Registry Key:
<MSDEGUID> = HKLM\SOFTWARE\Microsoft\Microsoft SQL
Server\<YourInstanceName>\Setup\ProductCode
2. Execute: msiexec.exe /x "<MSDEGUID>"
I've not found a way to _completely_ remove MSDE programmatically, but the
above may do what you need. Hope it helps.
- Jeff
"Subhojit Banerjee" <SubhojitBanerjee@.discussions.microsoft.com> wrote in
message news:A60CFD94-FC3F-4EA0-BDC1-3D490579BA7C@.microsoft.com...[vbcol=seagreen]
> Hi Girish,
> Thanks for replying.
> **** "Just a request. Please read the posting before replying." ****
> I really need to know how to uninstall MSDE instance "programatically" and
> "not Manually".
> Regards
>
> "Girish Sundaram" wrote:
folder[vbcol=seagreen]
Microsoft[vbcol=seagreen]
registry[vbcol=seagreen]
keys:[vbcol=seagreen]
Server\<INSTANCENAME>[vbcol=seagreen]
ProductCode[vbcol=seagreen]
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\MSSQL$<INSTANCENAME>[vbcol=seagreen]
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\SQLAgent$<INSTANCENAME>[vbcol=seagreen]
rights.[vbcol=seagreen]

Copying tables, MSDE

Hi!

I've got a very simple problem I can't find an answere to.

I've got an MSDE database and I want to copy a table.

I've tried something like:

create table2 as select * from table1

with and without the "as", but I can't get it to work and I can't find a good answere on the internet.

very thankful for an answere!

/Jon

hello..

use SELECT INTO statement:
from MSDN:

The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server.
sample code:
SELECT * INTO table2
FROM table1

|||ok, almost there...
it works, except for the keys.
how do I make the primary keys be primary keys in the copied table aswell?
Thanx!
/J|||

(sorry if this reply got posted twice)

thanx,

is it possible to get the old primary keys to be primary keys in the new table aswell?

The newly created table does not contain any kesy at this moment.

/jon

Copying tables

I am able to query several different msde databases on my network using
Query Analyzer from my pc. Using SQL Query Analyzer from my pc, I need to
copy a table from a database found on my local msde installation to a
database on another pc's msde installation. I was trying to find the SQL
Query syntax on Books Online but I had no luck. Can you help?
Thanks,
Ademar Nunes
Hi,
See BCP OUT and BCP IN in books online.
Thanks
Hari
SQL Server MVP
"Ademar" <Ademar@.noneofyourbusiness.com> wrote in message
news:uo2iGb6uEHA.1260@.TK2MSFTNGP12.phx.gbl...
>I am able to query several different msde databases on my network using
> Query Analyzer from my pc. Using SQL Query Analyzer from my pc, I need
> to
> copy a table from a database found on my local msde installation to a
> database on another pc's msde installation. I was trying to find the SQL
> Query syntax on Books Online but I had no luck. Can you help?
> --
> Thanks,
> Ademar Nunes
>
|||I did, but I was unable to make it work. I tried again, and I'm still
unable. Can you help?
Thanks,
Ademar Nunes
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eFagfq8uEHA.568@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi,
> See BCP OUT and BCP IN in books online.
>
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Ademar" <Ademar@.noneofyourbusiness.com> wrote in message
> news:uo2iGb6uEHA.1260@.TK2MSFTNGP12.phx.gbl...
SQL
>
sql

Copying MSDE database to disk - how?

I have my little MSDE on my computer. I can add and delete stuff from it through Web Matrix. My question is, where exactly is the database stored in Windows?? Also, say I wanted to copy it to a disk so I could show it to someone else, what file(s) would I need to copy?

The reason why I ask is because I have been doing a website as a project and we have to hand in all files on a disk. Getting all the .aspx & .ascx and the .config file is easy, but the database & stored procedures is proving tricky to find.

ThanksTry using this tool - http://www.webattack.com/get/dbamgr2.html its a MSDE Manager. Install it then connect to your server. right click the database you want to backup then select 'Backup Database' and bobs your uncle:)|||Actually you shold use that tool, right click your DB then click 'Generate SQL Script'. This will be easier to install on a remote server

Copying MSDE database into SQL 2005

I have a legasy database developed for SQL 2000 running in MSDE. I've tried COPY DATABASE into SQL 2005 (Developer edition, to see if I can get it to work right before specing out a new server), but the user accounts don't copy and I also suspect my settings for security and permissions aren't right.

I can set SQL server security to mixed mode, and the legasy system front end Access projects to connect using Windows authentication only, but then I'm unable to control access and set roles. I need to be able to connect as a particular user in order to admin the legasy system.

Is there a fairly uncomplicated way to copy user accounts and set SQL Server options to assign roles/permissions for an MSDE legasy system copy to SQL 2005?

These articles should shed a little light on the issues:

http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872Resolve Permission Issues -Database Is Moved Between SQL Servers

Copying MSDE database

I have a MSDE database on a laptop and need to copy it to another machine in order to use Access 2000 to inspect the tables and view the data using an Access project adp file.
Could someone please tell me how to do this and whether there are any relevant issues/problems.
thanksBACKUP and RESTORE work fine. There's just no GUI

You can do interactive tsql by Navigating to

C:\Program Files\Microsoft SQL Server\80\Tools\Binn

THEN type osql -E -r1

------------------------
Handy Instructions

Type in your tsql commands (BACKUP \RESTORE\Whatever)
For the TSQL reference, go to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_00_519s.asp

To execute these commands, on a line by itself type "GO"

to QUIT, on a line by itself type "QUIT"sql

Copying entire databases to remote server w/o Enterprise Manager

Is there any possible way to copy an entire MSDE database from my local system to a remote server using a program like 'osql'?

Or, is there any other GUI available for MSDE?

Thanks in advance.

GrierSure. There are a few ways. A couple that come to mind are to use the sp_detach_db system stored procedure in osql, copy the database files to the server, and use sp_attach_db to reattach them. Another is to run dtsrun.exe to run a DTS package that copies it over. You could also write ADO.NET code to do it, but it would be a lot of work to get all the objects copied.

There are several tools you can use to administer MSDE:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

ASP.NET WebMatrix (which includes a database management tool) from this web site (click on the Web Matrix tab at the top of this page).

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also access MSDE using Access. I'm not sure if this will do what you want, though.

Any of these work for you?
Don

Copying DB from one instance to another

Hi,

I want to copy the "Example" database of SQL 2000(default instance) into MSDE (named instance).

Both are installed on the same machine.

HOw to do this?

Thank You!

Use the copy database wizard. The SQL Server Tools General forum is probably the best place to ask about that http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1 and it is covered in Books Online http://msdn2.microsoft.com/en-us/library/ms188664.aspx

Donald Farmer

Copying database from one instance to another

I am very very new at SQL and MSDE, however I have the following question,
hopefully an easy one.
We have been using an instance of MSDE on a slower smaller PC and recently
installed the SQL Server 2005 Express Edition Beta 2. We are not yet fully
going to this Beta yet.
How do I copy databases including tables with data over to this new
instance?
Thanks for the information.
Brad
"Brad" <ballison@.ukcdogs.com> wrote in message
news:uLpPF79pEHA.2456@.TK2MSFTNGP10.phx.gbl...
> I am very very new at SQL and MSDE, however I have the following question,
> hopefully an easy one.
> We have been using an instance of MSDE on a slower smaller PC and recently
> installed the SQL Server 2005 Express Edition Beta 2. We are not yet
fully
> going to this Beta yet.
> How do I copy databases including tables with data over to this new
> instance?
I'd recommend running a BACKUP of your database from MSDE, then RESTORE to
your copy of SQL Server 2005.
Steve
|||I recommend using sp_detach_db to disconnect the source database from the
Master database. This permits you to copy the MDF and LDF files to another
system. Once copied you can reattach with SQL Enterprise Manager or
sp_attach_db. In SQL Server Express (SS 2005) you can also use ADO 2.0 to
open an MDF file directly which automatically attaches the database (and
log) files unless they are already in Master. Otherwise you can use
sp_attach_db on the target system.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:u6gBWt%23pEHA.192@.tk2msftngp13.phx.gbl...
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:uLpPF79pEHA.2456@.TK2MSFTNGP10.phx.gbl...
> fully
> I'd recommend running a BACKUP of your database from MSDE, then RESTORE to
> your copy of SQL Server 2005.
> Steve
>
|||That will certainly work, however one of the advantages of BACKUP/RESTORE is
only the data is copied from device to device. Database mdf and ldf files
will contain "empty" space.
Steve
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:%23A0beC$pEHA.2236@.TK2MSFTNGP09.phx.gbl...
> I recommend using sp_detach_db to disconnect the source database from the
> Master database. This permits you to copy the MDF and LDF files to another
> system. Once copied you can reattach with SQL Enterprise Manager or
> sp_attach_db. In SQL Server Express (SS 2005) you can also use ADO 2.0 to
> open an MDF file directly which automatically attaches the database (and
> log) files unless they are already in Master. Otherwise you can use
> sp_attach_db on the target system.
> hth
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> __________________________________
> "Steve Thompson" <stevethompson@.nomail.please> wrote in message
> news:u6gBWt%23pEHA.192@.tk2msftngp13.phx.gbl...
to
>
|||Steve,
Being new to MSDE is this something that would be done from the command
line? What would the syntax be?
Thanks for the help.
Brad
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:u6gBWt%23pEHA.192@.tk2msftngp13.phx.gbl...
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:uLpPF79pEHA.2456@.TK2MSFTNGP10.phx.gbl...
> fully
> I'd recommend running a BACKUP of your database from MSDE, then RESTORE to
> your copy of SQL Server 2005.
> Steve
>
|||Brad,
Yes, you can use the OSQL utility to do this, from a command line prompt
type:
osql
to get the command parameters. I find it easiest to create a batch script
with the sql syntax.
The syntax (and examples) for the other 2 commands: BACKUP and RESTORE you
can get from SQL Server BOL (as there are many options depending on what you
need),.
If you have not downloaded BOL, it's free and an excellent resource:
http://www.microsoft.com/sql/techinf...2000/books.asp
Steve
PS if you run into any issues, please post the sy
"Brad" <ballison@.ukcdogs.com> wrote in message
news:eV%23k3BtqEHA.2612@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Steve,
> Being new to MSDE is this something that would be done from the command
> line? What would the syntax be?
> Thanks for the help.
> Brad
>
> "Steve Thompson" <stevethompson@.nomail.please> wrote in message
> news:u6gBWt%23pEHA.192@.tk2msftngp13.phx.gbl...
to
>
|||hi Brad,
"Brad" <ballison@.ukcdogs.com> ha scritto nel messaggio
news:eV%23k3BtqEHA.2612@.TK2MSFTNGP15.phx.gbl
> Steve,
> Being new to MSDE is this something that would be done from the
> command line? What would the syntax be?
the syntax can be found at
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
but this can not be done directly by cmd... you have to resort on tools
able to connect to SQL Server, as, for SQL Server 2005, SQLCMD.exe, a
command line tool similar to OSQL.exe (documented in
http://msdn.microsoft.com/library/de..._osql_1wxl.asp)
please discuss SQL Server 2005 related problems/feauures int the public beta
newsgroups, that can be found at
http://communities.microsoft.com/new...r2005&slcid=us
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Copying database files

Hi all,
I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
that a developer must be able to upload/dowload via FTP the mdf and ldf
files.
This isn't a production database, so there is no active connection to it
except for the said developer.
The system doesn't even allow us to copy the files, saying that they are in
use.
I've fiddled with the "Auto Close" parameter for the db, but to no avail.
The only way I've found is to stop the SQL Server, but obviously, this isn't
a viable solution.
How can this be done?
TIA
Paul Dussault, MCP
Hi Paul,
The programmer mentioned below could detach the database remotely then copy
the files and last but not least; attach the database. You can do this
without stopping the service.
Yours sincerely,
Jo Segers.
"Paul Dussault" <paulduss@.hotmail.com> schreef in bericht
news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
> that a developer must be able to upload/dowload via FTP the mdf and ldf
> files.
> This isn't a production database, so there is no active connection to it
> except for the said developer.
> The system doesn't even allow us to copy the files, saying that they are
in
> use.
> I've fiddled with the "Auto Close" parameter for the db, but to no avail.
> The only way I've found is to stop the SQL Server, but obviously, this
isn't
> a viable solution.
> How can this be done?
> TIA
> Paul Dussault, MCP
>
|||1. detach the database - sp_detach_db
2. copy the files
3. re-attach the database - sp_attach_db
If you do this on a regular basis, you can create a script to detach your
database, copy the files to an alternate location, then re-attach the
database on a regular basis.
Regards
Shane Brodie
"Paul Dussault" <paulduss@.hotmail.com> wrote in message
news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
> that a developer must be able to upload/dowload via FTP the mdf and ldf
> files.
> This isn't a production database, so there is no active connection to it
> except for the said developer.
> The system doesn't even allow us to copy the files, saying that they are
in
> use.
> I've fiddled with the "Auto Close" parameter for the db, but to no avail.
> The only way I've found is to stop the SQL Server, but obviously, this
isn't
> a viable solution.
> How can this be done?
> TIA
> Paul Dussault, MCP
>
|||Thank you both for your quick replies.
I was aware of the sprocs, but the developper won't have any other access
than HTTP and FTP (ports 80 and 21).
Should I create an ASP script to execute these sprocs and then let the
developper execute to script as needed?
Thanks again!
Paul Dussault, MCP
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
> If you do this on a regular basis, you can create a script to detach your
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
> Regards
> Shane Brodie
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
> in
avail.
> isn't
>
|||A much better option is to use the BACKUP command to backup your =
database(s) to a file. You can use these files to restore on your =
machine. The benefit with this approach is that you do not have to take =
the database offline at any point.
More information about Backup and Restore can be found within Books =
Online
http://www.microsoft.com/sql/techinf...2000/books.asp
--=20
Keith
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message =
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
>=20
> If you do this on a regular basis, you can create a script to detach =
your[vbcol=seagreen]
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
>=20
> Regards
>=20
> Shane Brodie
>=20
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
ldf[vbcol=seagreen]
to it[vbcol=seagreen]
are[vbcol=seagreen]
> in
avail.[vbcol=seagreen]
this
> isn't
>=20
>
|||In 6 years of SQL server experience, I've never found a case where using
backup/restore was better than using detach/attach when wholesale
replacement of the database is needed. Additionally, in order to restore
from a backup, the restorer still needs to have access to the SQL server
enterprise manager and the database has to be void of any users or pending
transactions.
Paul's situation indicates only HTTP and FTP access is available. So, his
idea of exposing a script to attach and detach the relevant database files
should work OK. My question is, "What is the developer doing that requires
him/her to make regular wholesale replacements of the entire database?"
-Steve
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eqZb2V9IEHA.700@.TK2MSFTNGP09.phx.gbl...
A much better option is to use the BACKUP command to backup your database(s)
to a file. You can use these files to restore on your machine. The benefit
with this approach is that you do not have to take the database offline at
any point.
More information about Backup and Restore can be found within Books Online
http://www.microsoft.com/sql/techinf...2000/books.asp
Keith
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
> If you do this on a regular basis, you can create a script to detach your
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
> Regards
> Shane Brodie
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
> in
avail.
> isn't
>
|||sp_detach_db takes the database offline.=20
This is unacceptable in a production environment.
BACKUP does not bring the database offline.
The developer/DBA has many options when restoring the database (WITH =
MOVE, for example)
SQL Server Enterprise Manager is not required to issue the RESTORE =
command. Anything that can execute the appropriate Transact-SQL RESTORE =
command will do. This includes osql.exe (a command line utility), Query =
Analyzer, or even a query window within a web page.
I agree with your question "What is the developer doing that requires =
him/her to make regular wholesale replacements of the entire database?"
Perhaps they are not tracking their table, data, stored procedure =
changes and it is simply "easier" to replace the whole database when =
they want to move their code to production. Most of us would agree that =
this is not the best method of code promotion. It is better to apply =
table changes as necessary, insert/update/delete any data that needs to =
be modified, and create the stored procedures that have changed since =
the latest build and promote to production.
--=20
Keith
"Steve Lupton" <nospam@.nowhere.com> wrote in message =
news:XqZfc.18538$_I3.13377@.twister.socal.rr.com...
> In 6 years of SQL server experience, I've never found a case where =
using
> backup/restore was better than using detach/attach when wholesale
> replacement of the database is needed. Additionally, in order to =
restore
> from a backup, the restorer still needs to have access to the SQL =
server
> enterprise manager and the database has to be void of any users or =
pending
> transactions.
>=20
> Paul's situation indicates only HTTP and FTP access is available. So, =
his
> idea of exposing a script to attach and detach the relevant database =
files
> should work OK. My question is, "What is the developer doing that =
requires
> him/her to make regular wholesale replacements of the entire =
database?"
>=20
> -Steve
>=20
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eqZb2V9IEHA.700@.TK2MSFTNGP09.phx.gbl...
> A much better option is to use the BACKUP command to backup your =
database(s)
> to a file. You can use these files to restore on your machine. The =
benefit
> with this approach is that you do not have to take the database =
offline at
> any point.
>=20
> More information about Backup and Restore can be found within Books =
Online[vbcol=seagreen]
> http://www.microsoft.com/sql/techinf...2000/books.asp
>=20
> --=20
> Keith
>=20
>=20
> "Shane Brodie" <sbrodie@.decorkit.com> wrote in message
> news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...
your[vbcol=seagreen]
the[vbcol=seagreen]

Copying database files

Hi all,
I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
that a developer must be able to upload/dowload via FTP the mdf and ldf
files.
This isn't a production database, so there is no active connection to it
except for the said developer.
The system doesn't even allow us to copy the files, saying that they are in
use.
I've fiddled with the "Auto Close" parameter for the db, but to no avail.
The only way I've found is to stop the SQL Server, but obviously, this isn't
a viable solution.
How can this be done?
TIA
Paul Dussault, MCP
Hi Paul,
The programmer mentioned below could detach the database remotely then copy
the files and last but not least; attach the database. You can do this
without stopping the service.
Yours sincerely,
Jo Segers.
"Paul Dussault" <paulduss@.hotmail.com> schreef in bericht
news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
> that a developer must be able to upload/dowload via FTP the mdf and ldf
> files.
> This isn't a production database, so there is no active connection to it
> except for the said developer.
> The system doesn't even allow us to copy the files, saying that they are
in
> use.
> I've fiddled with the "Auto Close" parameter for the db, but to no avail.
> The only way I've found is to stop the SQL Server, but obviously, this
isn't
> a viable solution.
> How can this be done?
> TIA
> Paul Dussault, MCP
>
|||1. detach the database - sp_detach_db
2. copy the files
3. re-attach the database - sp_attach_db
If you do this on a regular basis, you can create a script to detach your
database, copy the files to an alternate location, then re-attach the
database on a regular basis.
Regards
Shane Brodie
"Paul Dussault" <paulduss@.hotmail.com> wrote in message
news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
> that a developer must be able to upload/dowload via FTP the mdf and ldf
> files.
> This isn't a production database, so there is no active connection to it
> except for the said developer.
> The system doesn't even allow us to copy the files, saying that they are
in
> use.
> I've fiddled with the "Auto Close" parameter for the db, but to no avail.
> The only way I've found is to stop the SQL Server, but obviously, this
isn't
> a viable solution.
> How can this be done?
> TIA
> Paul Dussault, MCP
>
|||Thank you both for your quick replies.
I was aware of the sprocs, but the developper won't have any other access
than HTTP and FTP (ports 80 and 21).
Should I create an ASP script to execute these sprocs and then let the
developper execute to script as needed?
Thanks again!
Paul Dussault, MCP
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
> If you do this on a regular basis, you can create a script to detach your
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
> Regards
> Shane Brodie
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
> in
avail.
> isn't
>
|||A much better option is to use the BACKUP command to backup your =
database(s) to a file. You can use these files to restore on your =
machine. The benefit with this approach is that you do not have to take =
the database offline at any point.
More information about Backup and Restore can be found within Books =
Online
http://www.microsoft.com/sql/techinf...2000/books.asp
--=20
Keith
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message =
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
>=20
> If you do this on a regular basis, you can create a script to detach =
your[vbcol=seagreen]
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
>=20
> Regards
>=20
> Shane Brodie
>=20
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
ldf[vbcol=seagreen]
to it[vbcol=seagreen]
are[vbcol=seagreen]
> in
avail.[vbcol=seagreen]
this
> isn't
>=20
>
|||In 6 years of SQL server experience, I've never found a case where using
backup/restore was better than using detach/attach when wholesale
replacement of the database is needed. Additionally, in order to restore
from a backup, the restorer still needs to have access to the SQL server
enterprise manager and the database has to be void of any users or pending
transactions.
Paul's situation indicates only HTTP and FTP access is available. So, his
idea of exposing a script to attach and detach the relevant database files
should work OK. My question is, "What is the developer doing that requires
him/her to make regular wholesale replacements of the entire database?"
-Steve
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eqZb2V9IEHA.700@.TK2MSFTNGP09.phx.gbl...
A much better option is to use the BACKUP command to backup your database(s)
to a file. You can use these files to restore on your machine. The benefit
with this approach is that you do not have to take the database offline at
any point.
More information about Backup and Restore can be found within Books Online
http://www.microsoft.com/sql/techinf...2000/books.asp
Keith
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
> If you do this on a regular basis, you can create a script to detach your
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
> Regards
> Shane Brodie
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
> in
avail.
> isn't
>
|||sp_detach_db takes the database offline.=20
This is unacceptable in a production environment.
BACKUP does not bring the database offline.
The developer/DBA has many options when restoring the database (WITH =
MOVE, for example)
SQL Server Enterprise Manager is not required to issue the RESTORE =
command. Anything that can execute the appropriate Transact-SQL RESTORE =
command will do. This includes osql.exe (a command line utility), Query =
Analyzer, or even a query window within a web page.
I agree with your question "What is the developer doing that requires =
him/her to make regular wholesale replacements of the entire database?"
Perhaps they are not tracking their table, data, stored procedure =
changes and it is simply "easier" to replace the whole database when =
they want to move their code to production. Most of us would agree that =
this is not the best method of code promotion. It is better to apply =
table changes as necessary, insert/update/delete any data that needs to =
be modified, and create the stored procedures that have changed since =
the latest build and promote to production.
--=20
Keith
"Steve Lupton" <nospam@.nowhere.com> wrote in message =
news:XqZfc.18538$_I3.13377@.twister.socal.rr.com...
> In 6 years of SQL server experience, I've never found a case where =
using
> backup/restore was better than using detach/attach when wholesale
> replacement of the database is needed. Additionally, in order to =
restore
> from a backup, the restorer still needs to have access to the SQL =
server
> enterprise manager and the database has to be void of any users or =
pending
> transactions.
>=20
> Paul's situation indicates only HTTP and FTP access is available. So, =
his
> idea of exposing a script to attach and detach the relevant database =
files
> should work OK. My question is, "What is the developer doing that =
requires
> him/her to make regular wholesale replacements of the entire =
database?"
>=20
> -Steve
>=20
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eqZb2V9IEHA.700@.TK2MSFTNGP09.phx.gbl...
> A much better option is to use the BACKUP command to backup your =
database(s)
> to a file. You can use these files to restore on your machine. The =
benefit
> with this approach is that you do not have to take the database =
offline at
> any point.
>=20
> More information about Backup and Restore can be found within Books =
Online[vbcol=seagreen]
> http://www.microsoft.com/sql/techinf...2000/books.asp
>=20
> --=20
> Keith
>=20
>=20
> "Shane Brodie" <sbrodie@.decorkit.com> wrote in message
> news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...
your[vbcol=seagreen]
the[vbcol=seagreen]

copying data folder to another instance

How do I copy the data from one instance of an MSDE to another? What I need
to do is write the structure, data, etc on a CD so I can copy this back on
my server instance at home and work on it.
Thanks for the information.
Brad
Attach/Detach or Backup/Restore
"Brad" <ballison@.ukcdogs.com> wrote in message
news:OijL5KEEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> How do I copy the data from one instance of an MSDE to another? What I
need
> to do is write the structure, data, etc on a CD so I can copy this back on
> my server instance at home and work on it.
> Thanks for the information.
> Brad
>
|||Norman,
I primarily progam and I really am not a DB Admin. I understand the
concepts of Attach/Detach but I have never practically used it. How would I
get into the database to do that? Is the Backup/Restore the Backup that is
used through Windows?
I know I can get into it with OSQL, right? When I do try this opening a
command window, typing OSQL -U sa, then the sa password, I get the following
error:
[Shared Memory]SQL Server does not exist or access denied
[Shared Memory]ConnectionOpen (Connect())
Thanks for the information.
Brad
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:eXwJ7OEEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Attach/Detach or Backup/Restore
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:OijL5KEEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> need
>
|||Norman,
In doing some research I changed OSQL -U to OSQL -E -S <server\instance> and
it worked.
I am now using BACKUP and RESTORE.
Thanks for the information.
Brad
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:eXwJ7OEEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Attach/Detach or Backup/Restore
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:OijL5KEEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> need
>
|||Now I am getting an error message when I try to RESTORE. It looks as if
RESTORE is using the InstanceName from the Backup and I am trying to retore
to a different MSDE instance name on a different server. It gives me error
messages.
Did I do something wrong with the restore statement?
"Brad" <ballison@.ukcdogs.com> wrote in message
news:OTXJuiEEFHA.464@.TK2MSFTNGP15.phx.gbl...
> Norman,
> In doing some research I changed OSQL -U to OSQL -E -S <server\instance>
> and it worked.
> I am now using BACKUP and RESTORE.
> Thanks for the information.
> Brad
>
> "Norman Yuan" <NotReal@.NotReal.not> wrote in message
> news:eXwJ7OEEFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
|||Posting the error message would help, but you're probably trying to restore
a database whose physical location does not exists on the destination
computer. Look at the WITH MOVE qualifier of the RESTORE command. This will
allow you to the logical database files to a different physical location
than where they originated from. Another solution is to make the location
for the database files the same on all systems.
Jim
"Brad" <ballison@.ukcdogs.com> wrote in message
news:Ochhl5EEFHA.1600@.TK2MSFTNGP10.phx.gbl...
> Now I am getting an error message when I try to RESTORE. It looks as if
> RESTORE is using the InstanceName from the Backup and I am trying to
> retore to a different MSDE instance name on a different server. It gives
> me error messages.
> Did I do something wrong with the restore statement?
>
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:OTXJuiEEFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||Jim,
Thanks for the information. This is the error message that I get:
File 'CLASSICSQL' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$UKCSQL\Data\CLASSICSQL.mdf'. Use WITH MOVE to identify a valid
location for the file.
The syntax I am using is "RESTORE DATABASE ced FROM DISK =
'D:\MSDEBack\CES.bak' "
This is when I get the error. What is the syntax using the WITH MOVE? or
where would I find this information?
Thanks,
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uOfJD%23FEFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Posting the error message would help, but you're probably trying to
restore
> a database whose physical location does not exists on the destination
> computer. Look at the WITH MOVE qualifier of the RESTORE command. This
will[vbcol=seagreen]
> allow you to the logical database files to a different physical location
> than where they originated from. Another solution is to make the location
> for the database files the same on all systems.
> Jim
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:Ochhl5EEFHA.1600@.TK2MSFTNGP10.phx.gbl...
gives[vbcol=seagreen]
<server\instance>[vbcol=seagreen]
I[vbcol=seagreen]
back
>
|||Jim,
Got it. Using osql for the first time and making sure there are not any
syntax errors is a pain.
Thanks again,
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uOfJD%23FEFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Posting the error message would help, but you're probably trying to
restore
> a database whose physical location does not exists on the destination
> computer. Look at the WITH MOVE qualifier of the RESTORE command. This
will[vbcol=seagreen]
> allow you to the logical database files to a different physical location
> than where they originated from. Another solution is to make the location
> for the database files the same on all systems.
> Jim
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:Ochhl5EEFHA.1600@.TK2MSFTNGP10.phx.gbl...
gives[vbcol=seagreen]
<server\instance>[vbcol=seagreen]
I[vbcol=seagreen]
back
>
|||hi,
AllcompPC wrote:
> Jim,
> Thanks for the information. This is the error message that I get:
> File 'CLASSICSQL' cannot be restored to 'C:\Program Files\Microsoft
> SQL Server\MSSQL$UKCSQL\Data\CLASSICSQL.mdf'. Use WITH MOVE to
> identify a valid location for the file.
> The syntax I am using is "RESTORE DATABASE ced FROM DISK =
> 'D:\MSDEBack\CES.bak' "
> This is when I get the error. What is the syntax using the WITH
> MOVE? or where would I find this information?
>
RESTORE DATABASE ced
FROM DISK = 'D:\MSDEBack\CES.bak'
WITH
MOVE Logical_File_Name_for_Data to 'C:\Program Files\Microsoft SQL
Server\MSSQL$UKCSQL\Data\CLASSICSQL.Mdf' ,
MOVE Logical_File_Name_for_Log to 'C:\Program Files\Microsoft SQL
Server\MSSQL$UKCSQL\Data\CLASSICSQL_Log.Ldf'
or the alike, specifying the logical file names and the destination physical
position you want the files to be restored to..
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

copying a DB from one server to another

Hi

I have a PC with MSDE2000 and need to copy the data to another PC running SQL2005.

I have registered the MSDE server on my SQL2005 PC and can see the tables etc.

Can this be done automatically, say every day?

Is there a way of mirroring the DB?

Cheers

Eugene

Hello,

http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx. You can find more info from this about copying the database from MSDE to SQL2005.

Thanks

|||

If your SQL Server 2005 machine is running Standard Edition or higher, you can create a package in SQL Server Integration Services to automate moving data. You can schedule running the package using SQL Server Agent.

Database replication (publisher/subscriber) requires higher SKUs (Workgroup and up) for the publisher. SQL Server 2005 Express can be a subscriber though. You can see a comparison of features here: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Hope this helps,
Steve

|||

Hi Steve

I Am using 2005 Std, I have also just installed SP1

I was hoping to use the mirroring function but it looks like it only works with sql 2005 DB! The same is true of Log shipping I think?

So is there any samples of how you use this integration service?

I am not that familiar with SQL as a whole and it would be good if there we're simple ways around the problems.

I would of thought there is an easy way to automatically copy a DB to another Server , like wizards....

Any Help Would be Appreciated

Cheers

Eugene

Copy table structure to a new table

Hello everyone,

I have a local MSSQL server (I guess it's called MSDE), with some tables that I would like to use as a template for a set of new tables. I would simply like a Stored Procedure that takes these 3 tables, makes a copy of their structure (not data, since they will be empty), and name them by using a parameter given to the SP. I have made something that I thought would work, but after testing it a bit more, it seems to forget default values for the fields, which is of course not good enough :). I hope that someone can tell me how to duplicate these 3 tables, including every detail for the fields!

This is not really a trivial task in T-SQL. You can probablycobble something together using the INFORMATION_SCHEMA views and someheavy dynamic SQL, but it may be easier to simply hardcode the tables'DDL into your stored procedure.
Or, you could use DMO to help you. See if this helps:
http://www.nigelrivett.net/DMO/DMOScripting.html