Identity column info gets lost

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Identity column info gets lost

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-11-12 : 09:52:15

Hi,

I just tried to export a database from one SQL server to another, I have around 100 tables and the first column in each is always id with identity set to true.

On the destination server all the identity flags are set to false, how can I fix this?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 10:00:53

try if you can set identity property from mgmnt studio.
http://www.mssqltips.com/tip.asp?tip=1397

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-11-12 : 10:09:18

Yes I can edit the column and switch the identity property back on, and it seems to work fine. Is there no way to copy the identity flags during the import/export process?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 10:27:19

have you tried with copy database wizard?

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-11-12 : 10:28:03

Where do I fine that?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-12 : 10:34:49

quote:
Originally posted by visakh16

have you tried with copy database wizard?

I wouldn't recommend Copy database wizard. You can go with backup/restore method as simple as it is.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 10:34:50

Its a wizard available in toolbox of SSIS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 10:37:38

quote:
Originally posted by sodeep

quote:
Originally posted by visakh16

have you tried with copy database wizard?

I wouldn't recommend Copy database wizard. You can go with backup/restore method as simple as it is.

Could you please specify the reason?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-12 : 10:40:45

Please see this:

http://blog.zygonia.net/PermaLink,guid,0de5f65a-9951-41c7-a514-3a0a40c093cd.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 10:51:06