Reseeding tables with an identity column

Ben Mills

One thing that SQL Compare or SQL Data Compare doesn't seem to do is synchronize the seed value on tables with identity columns. This value can be determined by calling DBCC CHECKIDENT('TableName', NORESEED). I have a situation where I use SQL Data Compare to sync a local development database with a production database so that I can test with the latest data. After I run the sync, the seed values reported by DBCC CHECKIDENT('TableName', NORESEED) can be different. I'm worried that I can end up in a situation where the next identity value is less than some of the values stuffed into the development database as part of the sync. When new records are added to the development database, then their new identity value might already be used and a primary key violation will occur.

I feel like SQL Data Compare should do a DBCC CHECKIDENT('TableName', RESEED, SeedValueFromSourceDb) on the target table.

Thanks,
Ben

Brian Donahue

Hi Ben,

That's a great idea! We'll look into doing that. It should improve the consistency of the tables after a Data Compare has been run.