I have a DTS that runs every night that copies tables from one SQL Server to another for using in a "backup-system" of an application.
I am doing a Transform Data Task from the one table to an exact copy of the table on the other server for several tables. The problem I have is that the primary key on all the tables on the back-up system are all re-numbered and causes all the normalization to be incorrect between tables.
Can I remove the "Identity Increment" off all these tables before the copy and then put it on after the copy? What T-SQL statements would I run to do both removal and adding them back on in the DTS?