I appreciated the idea in this article.

I thought I would also share another option. I routinely re-create my databases from scratch when I need to make major schema changes to the database. This identity change would count as such a change. The steps are:

1) make changes to the data model as needed

2) forward engineer a new database that is just like the original db only it has the schema changes you want.

3) run all the code (stored procs, triggers, etc.) on new db

4) run some code that automatically creates the code for a stored proc which copies table data from the source database into the new database

5) tweak the copy code as needed and run it

>> At this point, the new database is identical to the old database except for the changes you have deliberately made. So:

6) backup the new database and restore over the original database.

The advantages are that you follow the same set of steps every time. You don't have to say look for the set of indexes and foreign keys that are specifically attached to the one table you are working with. Also, you can make a whole bunch of minor and major changes to your database at once with the same set of steps. One of my favorite advantages is that you make sure your database matches your data model, and you know that your source code (stored procs, functions, etc.) are all up to date. In other words, you know that you can re-create the schema for the production database as needed at any time. The parallel is like always having a fully working build at your fingertips as application developers try to do.

All of the steps above have to be done when no one is using the database. Thus, this option may not be practical for larger databases and longer up-times. I have relatively small databases (under a gigabite) and up-time is only 7-6 on weekdays. So, it is easy for me to have "down time" and work on weekends. For other agencies in my situation, this is an approach to think about for any type of significant schema change--and it works in any version of SQL Server.