Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to compare two databases?

I have two databases that are similar, but not the same. DB 1 is the old one and DB2 is the updated one with lots of new tables, columns, procs, constraint etc.

I need to write an update script in order to update DB1 database. These databases have lots of tables and stored procedures. Is there any way to get the differences in two database other than manually.

like image 776
Jango Avatar asked Jul 29 '10 14:07

Jango


People also ask

How do I compare two db schemas?

To compare database definitions. On the Tools menu, select SQL Server, and then click New Schema Comparison. Alternatively, right-click the TradeDev project in Solution Explorer, and select Schema Compare. The Schema Compare window opens, and Visual Studio automatically assigns it a name such as SqlSchemaCompare1 .

What is database comparison?

What Database Compare can do. Use Database Compare to find design changes between two Access desktop database files or two versions of the same file. Database Compare alerts you to additions, deletions, or renaming of objects such as tables, queries, forms, reports, and VBA modules.


3 Answers

Buy red-gate's SQL Compare. You wil never regret spending the money.

like image 93
HLGEM Avatar answered Oct 29 '22 09:10

HLGEM


There are a number of software tools (e.g. Red Gate) that enable you to compare databases easily.

Also, try this script; it's free and it should also work.

Cheers

like image 37
João Pereira Avatar answered Oct 29 '22 08:10

João Pereira


To pile on to everyone suggesting Red Gate's tool, it should be noted that this tool can generate scripts to update the second database.

The awesome thing about red gate (besides the fact that they're an SO sponsor...) is that if you have the SQL Toolbelt with an active subscription, you will automatically get any new tools added. They recently released SQL Source Control, and I got it included as part of my SQL Toolbelt subscription.

like image 43
Tim Coker Avatar answered Oct 29 '22 09:10

Tim Coker