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

Microsoft.SqlServer.Management.Smo Backup and Restore classes

Hi,

Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.

From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?

And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.

Thanks!

Without full backup transaction log backup is useless. After processing full backup database is marked, so server allows log backups. No any search of backup files, of course.

create database [dummy];
go

-- here will be an error because there is no full backup
backup log [dummy] to disk = 'delete.it.bak'
go

-- this works fine
backup database [dummy] to disk = 'delete.it.bak'
exec xp_cmdshell 'move d:\databases\mssql.1\mssql\backup\delete.it.bak d:\databases\mssql.1\mssql\backup\dont.delete.it.bak'
backup log [dummy] to disk = 'delete.it.bak'
go

drop database [dummy]

To backup file in nondefault directory you should simply specify full path in backup device name. But make sure that SQL Server service account have access to such dir.

WBR, Evergray
--
Words mean nothing...

|||I want to backup to nondefault directory, so I tried

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup',
'C:\testing.Bak'

BACKUP DATABASE testDB TO testing_Backup

and received the following error message:

Cannot open backup device 'testing_Backup(C:\testing.Bak)'. Operating system error 5(Access is denied.).

I suppose this is due to insufficient access right of SQL Server service account, but what shall I do with it?

|||Windows Vista doesn't allow you to write in root (c:\).
Try it on d:\

Microsoft.SqlServer.Management.Smo Backup and Restore classes

Hi,

Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.

From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?

And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.

Thanks!

Without full backup transaction log backup is useless. After processing full backup database is marked, so server allows log backups. No any search of backup files, of course.

create database [dummy];
go

-- here will be an error because there is no full backup
backup log [dummy] to disk = 'delete.it.bak'
go

-- this works fine
backup database [dummy] to disk = 'delete.it.bak'
exec xp_cmdshell 'move d:\databases\mssql.1\mssql\backup\delete.it.bak d:\databases\mssql.1\mssql\backup\dont.delete.it.bak'
backup log [dummy] to disk = 'delete.it.bak'
go

drop database [dummy]

To backup file in nondefault directory you should simply specify full path in backup device name. But make sure that SQL Server service account have access to such dir.

WBR, Evergray
--
Words mean nothing...

|||I want to backup to nondefault directory, so I tried

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup',
'C:\testing.Bak'

BACKUP DATABASE testDB TO testing_Backup

and received the following error message:

Cannot open backup device 'testing_Backup(C:\testing.Bak)'. Operating system error 5(Access is denied.).

I suppose this is due to insufficient access right of SQL Server service account, but what shall I do with it?

|||Windows Vista doesn't allow you to write in root (c:\).
Try it on d:\

Microsoft.SqlServer.Management.Smo Backup and Restore classes

Hi,

Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.

From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?

And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.

Thanks!

Without full backup transaction log backup is useless. After processing full backup database is marked, so server allows log backups. No any search of backup files, of course.

create database [dummy];
go

-- here will be an error because there is no full backup
backup log [dummy] to disk = 'delete.it.bak'
go

-- this works fine
backup database [dummy] to disk = 'delete.it.bak'
exec xp_cmdshell 'move d:\databases\mssql.1\mssql\backup\delete.it.bak d:\databases\mssql.1\mssql\backup\dont.delete.it.bak'
backup log [dummy] to disk = 'delete.it.bak'
go

drop database [dummy]

To backup file in nondefault directory you should simply specify full path in backup device name. But make sure that SQL Server service account have access to such dir.

WBR, Evergray
--
Words mean nothing...

|||I want to backup to nondefault directory, so I tried

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup',
'C:\testing.Bak'

BACKUP DATABASE testDB TO testing_Backup

and received the following error message:

Cannot open backup device 'testing_Backup(C:\testing.Bak)'. Operating system error 5(Access is denied.).

I suppose this is due to insufficient access right of SQL Server service account, but what shall I do with it?

|||Windows Vista doesn't allow you to write in root (c:\).
Try it on d:\

Microsoft.SqlServer.Management.Smo Backup and Restore classes

Hi,

Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.

From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?

And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.

Thanks!

Without full backup transaction log backup is useless. After processing full backup database is marked, so server allows log backups. No any search of backup files, of course.

create database [dummy];
go

-- here will be an error because there is no full backup
backup log [dummy] to disk = 'delete.it.bak'
go

-- this works fine
backup database [dummy] to disk = 'delete.it.bak'
exec xp_cmdshell 'move d:\databases\mssql.1\mssql\backup\delete.it.bak d:\databases\mssql.1\mssql\backup\dont.delete.it.bak'
backup log [dummy] to disk = 'delete.it.bak'
go

drop database [dummy]

To backup file in nondefault directory you should simply specify full path in backup device name. But make sure that SQL Server service account have access to such dir.

WBR, Evergray
--
Words mean nothing...

|||I want to backup to nondefault directory, so I tried

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup',
'C:\testing.Bak'

BACKUP DATABASE testDB TO testing_Backup

and received the following error message:

Cannot open backup device 'testing_Backup(C:\testing.Bak)'. Operating system error 5(Access is denied.).

I suppose this is due to insufficient access right of SQL Server service account, but what shall I do with it?

|||Windows Vista doesn't allow you to write in root (c:\).
Try it on d:\

Microsoft.ReportingServices.UI ?

does microsoft give away the source for the reporting service ui?
usually installed in
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportManager\Pages
I would love to see the code for this page
<%@. Register TagPrefix="MSRS" Namespace="Microsoft.ReportingServices.UI"
Assembly="ReportingServicesWebUserInterface" %>
<%@. Page language="c#" Codebehind="DataSource.aspx.cs"
AutoEventWireup="false"
Inherits="Microsoft.ReportingServices.UI.DataSourcePage" %>
If they don't give this code away they should it would be very very helpfull.We all wish. But there is some sample UI code you can download.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:8A7902B5-5496-458B-B323-013B9724EF53@.microsoft.com...
> does microsoft give away the source for the reporting service ui?
> usually installed in
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportManager\Pages
> I would love to see the code for this page
> <%@. Register TagPrefix="MSRS" Namespace="Microsoft.ReportingServices.UI"
> Assembly="ReportingServicesWebUserInterface" %>
> <%@. Page language="c#" Codebehind="DataSource.aspx.cs"
> AutoEventWireup="false"
> Inherits="Microsoft.ReportingServices.UI.DataSourcePage" %>
> If they don't give this code away they should it would be very very
> helpfull.