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 ?