December 8, 2005 at 4:32 am
Does anyone know why if I do a "copy database" from SQL Server 2000 to 2005, everything is correctly copied over except for the table identities? Is this a bug or is it designed like this? If it is supposed to be like this, is there any way I can easily update all my tables so that the primary keys are all unique identity columns?
May 14, 2006 at 11:22 am
You'd think they'd have this sorted out by now, considering it's one of the first things anyone upgrading from SQL 2K to 2K5 on a new server would need to do. I haven't found a way to do it using SSIS Copy or Import, even after SP1, and it seems no one else has either.
Restoring from a full SQL 2K backup brings back all the extending properties such as identity, default values and primary keys, but you'll have to carefully negotiate through all the gotchas of the process. Remember to set Compatibility level of SQL 2000 (80) for the SQL 2K5 db first, if you want all your old queries to continue working. To re-use the same user name on the new server, you'll have to delete the imported user of the same name in the imported db, which first requires the deletion of the imported user's schemas. Then assign access permission to the SQL 2K5 user on the newly imported db.
Hopefully the development boys will soon have SSIS Copy and Import working the way people need it to work.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply