Data Transfer SQL2000 to SQL2008

[dannyra]

I have an older site on CT that has a SQL2000 database. I have to upgrade my database to 2005 or 2008 before I can upgrade my hosting to a newer version of Coldfusion.

Most of my tables in 2000 use an identity field for the primary key. I'm using Microsoft SQL Server Management that I downloaded for free from Microsoft and ran several transfers and can't get the identity to carry over.

First I tried an export and import, but you can't add an identity to a field that already has data.
Second I tried to create the tables first in 2008 with a script and then importing the data. It won't allow this as you can't set an identity field.

I really don't want to have to create a new field as the identity as it will be a huge pain to go through my other tables and update the foreign keys as well.

Suggestions?

[jbcampos [CT]]

We could transfer over a .bak of your database for you if you like. Just shoot us a ticket. If your have a local SQL instance, you may want to run your database through the upgrade adviser just to be there will not be any conflicts:

http://www.microsoft.com/downloads/details.aspx?FamilyID=f5a6c5e9-4cd9-4e42-a21c-7291e7f0f852&displaylang=en

[CTForum]

I was able to make this migration by first scripting out all of my tables and creating them empty on the 2008 box and then I used the Import Data Wizard with the allow Identity Insert option in Edit Mappings.