Importing To New Server

Hello,

I am attempting to import a database from one server (2000) into another one (2005). I have tried a variety of methods to do so, but each seems to have at least one hiccup. The biggest frustration is that, depending upon how I setup the tables prior to import, one of two things ends up kicking me in the rear:

1) I leave the IDENTITY cols set as such, and even though I enable identity insert in the wizard (SQL Server 2005 Management Studio), they end up just incrementing starting at 1 as though it was fresh data. Obviously, once I apply the PKs and FKs, everything falls to pieces.

2) I initialize the identity cols as INT, and the data imports just fine. However, when I try to add the IDENTITY to the cols, it tells me to go screw myself. Actually, it just throws a syntax error. Here is the statement that I'm attempting:

ALTER TABLE myTable
ALTER COLUMN myCol IDENTITY
GO

I have also tried

ALTER TABLE myTable
ALTER COLUMN myCol INT IDENTITY
GO

I'm pulling my hair out with this one. It's played a rather significant role in me getting only 2 hours of sleep in the last 40+ hours. I really try not to post questions without exhausting myself trying to figure out the answer, or to find it with research (which is part of figuring it out). I am a moderator on EasyCFM.com's ColdFusion forums (mquack is the screenname), so I know how annoying it is when people don't put forth any effort on their own. Hopefully I don't come across here as being "one of them".

Any help or guidance to the *right* sources would be very much appreciated.

Thanks in advance. :-)Take backup and restore it to the server you want.

How much the database size are you having...?|||Thank you for the reply. It's only a few hundred megs, so it's not too terribly bad. Is there a way to change the schema that the backup restores to? Either that or a quick way to change the schema by script after the restore is done?|||Is there a way to change the schema that the backup restores to? Either that or a quick way to change the schema by script after the restore is done?

What exactly you want to change ?

Importing from Oracle 10g

I am trying to import a variety of data from an Oracle 10g database. When I import the data using the OLE DB it takes a long time (100K records an hour).
How can I improve the throughput of my import?
Is there a better driver I could be using?
Any input will be appreciated.
db55

Some interesting reading for you:

http://www.sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx

http://microsoftdw.blogspot.com/2005/11/how-to-load-oracle-with-ssisfast.html

-Jamie

|||The second link gives another link to a 3rd party solution that could work. Thank you for your response.|||

Please create a ADO.NET Connection manager and use Data Reader Source..there will be a sure Improvement, over oledb.

Is the Data transferred over the Congested network. This might also reduce the performance.

Please let me know the increase of the performnce, once you use Ado.net Connection manager.