How do I migrate table schemas from one DB to another without damaging the data in the destination DB?

Question

How do I migrate table schemas from one DB to another without damaging the data in the destination DB?

I want to move my data from my deployed development copy to the live database and would like to run some scripts to do it. I need to upgrade the schema for some tables and create others. I figure right now that I'll have to check each of the tables in the destination DB against the deployment one and then copy the new tables but that will be quite tedious. Are there any suggestions on how I can do this?

Answer 1

You can use a tool to generate the scripts. Redbrick has one, and Erwin can do deltas as well.

Answer 2

Check out SQL Compare tool by Redgate

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

You should be able to compare both the databases and then generate scripts based on the differences.

Answer 3

Another one besides the excellent Red-Gate SQL Compare is ApexSQL's SQL Diff.

ApexSQL also has a SQL Data Diff if you also need to compare and synchronize data from various sources.

Highly recommended!

Marc

Answer 4

In addition to the RedGate software mentioned above, Embarcadero Change Manager can do both schema and data instance compares, then generate alter scripts for the schemas and DML scripts for the data to bring two database in sync.