After restoring a database (2000) onto 2005, we noticed that the identity property is missing on all our tables. Is there a way to set this on all tables other than using GUI.

Thanks.
R

You could write a script to ALTER TABLE -check in Books Online for the complete syntax.

You did an restore of the database using RESTORE DATABASE and it lost the identity property? This is not something that should occur. I am pretty sure I have done this several times and not had a problem. You can also attach and detach the database and it will attach on 2005. I know for a fact that this will work because I have done this (that is why I didn't say I was 100% sure about the RESTORE method)

You could use SQL Compare to compare and synchronise the structures of the two (pre-upgrade and post-upgrade) databases. However you will have to have both of the databases on-line to be able to do this (the pre-upgrade database would have to sit on a SQL Server 2000 instance).

http://www.red-gate.com/products/SQL_Compare/index.htm

Red-Gate do offer a fully-functional trial version of the product.

Chris

This problem, most likely did not occur from using backup/restore. It occurs by using the "Generate SQL Script..." option and not selecting "Script Indexes", "Script Full Text", "Script triggers", and "Script Primary keys...." check boxes on the Options tab. These options are not selected as a default.

Specifically, the "Script Primary keys and Defaults..." causes you to loose the identity fields.

You can compare and synchronize SQL Server databases and data using ApexSQL Diff and/or DataDiff. Both these tools are commercial but there is a fully functional free trial.

It all depends how much is your data worth to you and are you willing to use third party tools.