Alright, so one of the issues my previous post did not address was the treatment of Identity columns when using BCP and BULK import.
By default, identity column data is not part of a BCP export, so when doing a BULK IMPORT SQL server will assign new values to the identity Column .
Fortunately, that is relatively easy to address, as decribed here:
http://msdn.microsoft.com/en-us/library/ms186335.aspx
Basically when doing a BCP export, add the "-E" parameter.
Example:
bcp Northwind.dbo.Authors out C:\Temp\DataExport.dat -n -E -T -S MPDESKTOP\SQL2005
And when importing, use the KEEPIDENTITY parameter in the BULK INSERT like this:
USE Northwind
GO
BULK INSERT Authors
FROM 'G:\Transfer\Tempfiles\DataExport.dat'
WITH (KEEPIDENTITY, DATAFILETYPE='native');
GO
This will preserve the values of identity columns when copying data between 2 servers/databases. Keep in mind that there still might be issue with referential integrity constraints etc. that you need to address when moving data between databases.
