Creating Database Update Scripts

I created an application based on an existing database, I made a lot of changes to the database, now I need to create scripts in the new database to use to update the old database. For example, I added 15 fields to 1 of the tables. Is there a way to use Tasks -> Generate Scripts to create a script that will check the existance of each column and create it if it does not exist? I tried multiple ways of doing this, but it will only create columns for tables that do not exist in the old database. If a table exists, none of the new columns are added.

I may be going about doing this wrong. The main goal I am tying to accomplish is to get all of the data that is in the old database (It was in use while the new one was being developed so there is a lot of data in the old database that I need to have in the new one) into the new one.

Am I better off creating a blank database, then exporting all of the data from the old database to the new one? Will this create any problems with my Primary Keys (The are all auto-increment Integers)?

The customary method would be to drop the existing table and add a new one with the correct structure, then restore data to it. But if your goal is really just to get the old data into the new database, a DTS job would do this or a simple SQL UPDATE query with the proper WHERE clause should work.

Jeff

|||The most reliable way to move a database is backup the old database and then restore it as the new database with the new name. Hope this helps.|||

The new database has a lot of new fields in it so restoring the backup would not work.

If I export the data, would I be able to import the data into the new database? All of the fields from the old database still exist, and would doing this change the auto increment Integer Primary Keys? and would doing this cause duplicate keys or is the database smart enough to see that they have been used?

|||If you restore the old database you have only the differences to move to the restored database but you can still use DTS to move the old database. In SQL Server IDENTITY is a property and you can drop it and set it with ALTER TABLE. Run a search for IDENTITY all in SQL Server BOL (books online). Hope this helps.