How can I dump & load MS-SQL preserving identity columns?

Question

I'd like to be able to dump & load a MS-SQL database so I can recreate the storage locations, the original database was poorly configured with 1 storage location and I'd like to correct this issue.

My current attemtps at doing this have resulted in the identity columns being regenerated upon a load, this was while using the `Tasks > Import' facility that comes with "SQL Management Studio".

Restoring from a backup keeps recreating the original storage locations.

I'm new to MS-SQL so please forgive me if this is a basic topic, but I haven't been able to find a solution anywhere. I'm currently more familiar with Progress OpenEdge RDBMS.

Answer 1

You're looking for SET IDENTITY_INSERT ON|OFF

Answer 2

You can use "bcp" from the command line to dump & load tables. The -E option on the command will retain identity values.

http://msdn.microsoft.com/en-us/library/aa337544.aspx