>>It's clumsy and a pain.

Yes for sure.
You can solve half the problem by setting up the SQL agent to do regular backups and grabbing the latest one to restore
I haven't seen a foolproof way to do it a table at a time, so I've reconciled myself to treating the DB as one big table.

>Why not back up and restore?
>
>>>I use Red Gate SQL Data Compare to duplicate my production data to a testing database. Basically it's a DELETE followed by a bunch of INSERTs. However, it seems to not reset the identity Primary key. I keep getting this error:
>
>{"Violation of PRIMARY KEY constraint 'Primary key_ApplicationNote'. Cannot insert duplicate key in object 'PreApplication.ApplicationNote'.\r\nThe statement has been terminated."}
>
>I know I can go in and run a script to reset the identity, but I have to do that on every table every time I do this. Does anybody know of a way to avoid this? Or a global way to reset all identity columns?

Very fitting: http://xkcd.com/386/

[View the parent message of this message]