Hello, I wanted to ask whether it is possible to compare the complete database structure of two huge databases. We have two databases, the one is a development database, the other a production database. I've sometimes forgotten to make changes in to the production database, before we released some parts of our code, which results that the production database doesn't have the same structure, so if we release something we got some errors. Is there a way to compare the two, or synchronize?
A:
Red-Gate SQL Compare is a great tool that will do this for you. I have used this for years with great success. It has saved me thousands of hours of work.
They also have a tool that will compare data as well. The product I mentioned above compares schema.
www-red-gate.com
Randy Minder
2010-03-05 19:41:01
Does Red Gate SQL Compare work on MySQL? I was under the impression it was MS SQL Server only.
Kibbee
2010-03-05 19:42:15
@Kibbe you right its just for mssql and not for mysql look here http://www.red-gate.com/about/index.htm
streetparade
2010-03-05 19:43:45
+1
A:
Depending on your database, the tools available vary.
I use Embarcadero's ER/Studio for this. It has a Compare and Merge feature.
There are plenty others, such as Toad for MySQL, that also have compare. Also agree on the Red-Gate suggestion, but never used it for MySQL.
mrjoltcola
2010-03-05 19:43:02
A:
Hi,
Red-Gate have some very nice tools for this. You can used SQL Compare or SQL Data Compare depending on what you want to do.
Adrian Faciu
2010-03-05 19:43:51
A:
SchemaCrawler is a free, platform-independent tool that allows you to compare database structures. The two databases do not need to be online at the same time - you can save your database structure into a human-readable text file. Comaprisons are done using standard diff tools.
I would recommend automating the comparison as a part of build and deploy process, to ensure that your production systems are never out of date.
Sualeh Fatehi
2010-03-05 21:46:02
A:
You can just dump them with --no-data and compare the files.
Remember to use the --lock-tables=0 option on your production database to avoid the big nasty global lock.
If you use the same mysqldump version (your dev and production systems should have the same software, right?) then you'll expect to get more-or-less identical files out. The tables will be in alpha order so a simple diff will show discrepancies up easily.
MarkR
2010-03-05 23:10:19
A:
I would definitely go with AdeptSQL if you're using MSSQL. It's the least good looking but the most talented db compare tool amongst the ones I've tried. It can compare both the structure and the data. It tells you which tables exist on one db but does not exist on the other, compares the structure and data of the common ones and it can produce the script to synchronize the two. It's not free but has a 30 day trial (as far as I can remember)
davsan
2010-03-24 09:13:32
A:
Try dbForge Scheme Compare for SQL server. It can compare and synchronize any database scheme. Quick, easy, always delivering a correct result. See how it flies on your database!
Devart
2010-04-12 10:42:31
A:
SQL Examiner Suite 2010 compares MySQL databases (both schema and data)
SQLDev
2010-05-13 09:28:13
+1
A:
DB Schema Difftective compares the structure between two MySQL databases and let you generate an sync script.