Migrate MSDE data to SQL 2000

I have local copy of MSDE 2000 on an XP workstation.
My program has already been reconfigured to point to a remote (from the XP
viewpoint) SQL 2000 server. As such, it has created the 2 databases that it
uses and is already populating those databases with data.
The question is, how do I get the "old" data from MSDE on the XP box into
the "new" databases on the remote SQL server?
When I try to use DTS and choosing the default of OLE Provider for SQL for
the source, I can't connect to the XP MSDE, which is using SQL Auth. I know
I am using the correct "sa" password for the MSDE database.
Robert
You have a few options: DTS, Detatch/Reattach, or Backup/Restore.
I prefer to backup on the old server, copy the backup file to the new
server, and restore it on the new server. This is fairly easy to do. Just
make sure you create the users from the old server on the new server first,
and then run sp_change_users_login to eliminate orphaned users after you do
the restore.
"Robert Gandrud" wrote:

> I have local copy of MSDE 2000 on an XP workstation.
> My program has already been reconfigured to point to a remote (from the XP
> viewpoint) SQL 2000 server. As such, it has created the 2 databases that it
> uses and is already populating those databases with data.
> The question is, how do I get the "old" data from MSDE on the XP box into
> the "new" databases on the remote SQL server?
> When I try to use DTS and choosing the default of OLE Provider for SQL for
> the source, I can't connect to the XP MSDE, which is using SQL Auth. I know
> I am using the correct "sa" password for the MSDE database.
> Robert
|||As far as I know, the only user is the "sa" user in the old MSDE database.
What then?
"Dan" wrote:
[vbcol=seagreen]
> You have a few options: DTS, Detatch/Reattach, or Backup/Restore.
> I prefer to backup on the old server, copy the backup file to the new
> server, and restore it on the new server. This is fairly easy to do. Just
> make sure you create the users from the old server on the new server first,
> and then run sp_change_users_login to eliminate orphaned users after you do
> the restore.
> "Robert Gandrud" wrote:
|||hi Robert,
Robert Gandrud wrote:
> I have local copy of MSDE 2000 on an XP workstation.
> My program has already been reconfigured to point to a remote (from
> the XP viewpoint) SQL 2000 server. As such, it has created the 2
> databases that it uses and is already populating those databases with
> data.
> The question is, how do I get the "old" data from MSDE on the XP box
> into the "new" databases on the remote SQL server?
> When I try to use DTS and choosing the default of OLE Provider for
> SQL for the source, I can't connect to the XP MSDE, which is using
> SQL Auth. I know I am using the correct "sa" password for the MSDE
> database.
>
MSDE installs by default disabling network protocols...in order to them
after installation you have to run the
Server Network Utility (svrnetcn.exe) and enabling the desired
protocol(s)...
Win XP Windows Firewall could be an issue too and please have a look at
http://support.microsoft.com/kb/841251/en-us in order to enable remote
connectivity...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql