sql server import export wizard - enable identity insert not working

Question

I am using sql server 2008.i have one ABC database on A server(source).now i want to copy all the schema and data of this database to my new database XYZ on B server(destination).for this i am using sql server import wizard.
now the scenario is:
In ABC database,many tables have identity columns as primary key.when i use import wizard, i set "Enable identity insert" to True(checked). but import process completes,i found that destination tables are created but primary keys are not set for tables and also identity is also not set.

It just copies the data,not the Primary keys or identity fields.

How to make it possible to work for "identity insertion" for all tables

Pls help.thanks in advance.

Answer 1

If you want to copy entire database better try Copy Database wizard, not Import/Export. This feature just copies data. Enable identity insert just tells the wizard to use SET IDENTITY_INSERT ON/OFF commands when inserting data to columns with IDENTITY - it doesn't set the IDENTITY parameter of the column.

Answer 2

try using these

SQL SCHEMA Comparison:

http://www.codeproject.com/KB/database/SQLCompare.aspx

SQL DATA Comparision

http://www.codeproject.com/KB/database/DataCompareTool.aspx