How to find the differences between the two databases?

Question

I am trying to copy changes from DatabaseA to DatabaseB, but I don’t know exactly what the changes are.

Is there an SQL script around that will find objects that differ between databases and then generate a script to update DatabaseB to match DatabaseA?

I am using SQL 2005 and I do not want to lose existing data in DatabaseB

Answer 1

There are Red Gate circuit and data comparison tools that do exactly what you are looking for. There are others on the market, such as Apex SQL Compare. But if you are fortunate enough to have a version of Visual Studio 2010 Premium or Ultimate, there is a comparison tool built into these versions.

http://www.apexsql.com/default.aspx

http://www.red-gate.com/products/sql-development/sql-compare

http://msdn.microsoft.com/en-us/library/dd193250.aspx

Otherwise, you will have to use linked servers and many queries to INFORMATION_SCHEMA.

Some of these software tools that you can use on a trial basis and are likely to get what you need without buying a full license.

Answer 2

Try DBComparer .

Answer 3

I used SQL Comparison and SQL Data Comparison with Red Gate Software , and they work like a charm. I do not assume that there is a script to do the same work, because if it existed, then there would be no use and market for these products.

Answer 4

I am using AdeptSQL Diff. This is a commercial product found at www.adeptsql.com . I think the trial license is designed for 30 days or 50 startups.