Compare Two Sql Databases
Today, I received an email from one of my blog readers, who would like to match test and production databases. Here is a basic script to compare two databases.
I'm currently developing an application using a MySQL database.
- Jul 12, 2016 How to compare two databases? SQL Server > Transact-SQL. Check this: Introducing Schema Compare for SQL Server Database Projects. There are some paid solutions as well, If you have too much money:-) * With that been said, in some cases as a result of your corruption, you might best to use simple query and do it manually.
- Total SQL Compare From Data to Schema Download SQL Comparison Toolset Today! Better manage data and schema changes for SQL Server and Oracle Database with SQL Comparison Toolset. Fully functional for 14 days. No credit card required (but all fields are).
The database-structure is still in flux and changes while development progresses (I change my local copy, leaving the one on the test-server alone).
Is there a way to compare the two instances of the database to see if there were any changes?
While currently simply discarding the previous test server database is fine, as testing starts entering test data it could get a bit tricky.
The same though more so will happen again later in production..
Is there an easy way to incrementally make changes to the production database, preferably by automatically creating a script to modify it?
Tools mentioned in the answers:
- Red-Gate's MySQL Schema & Data Compare (Commercial)
- Nob Hill Database Compare (Commercial)
- SQL EDT (Commercial)
closed as not constructive by WillJul 3 '12 at 14:30
As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. If this question can be reworded to fit the rules in the help center, please edit the question.
locked by Taryn♦Nov 18 '15 at 17:25
This question exists because it has historical significance, but it is not considered a good, on-topic question for this site, so please do not use it as evidence that you can ask similar questions here. This question and its answers are frozen and cannot be changed. More info: help center.
Read more about locked posts here.
21 Answers
If you're working with small databases I've found running mysqldump on both databases with the --skip-comments and --skip-extended-insert options to generate SQL scripts, then running diff on the SQL scripts works pretty well.
By skipping comments you avoid meaningless differences such as the time you ran the mysqldump command. By using the --skip-extended-insert command you ensure each row is inserted with its own insert statement. This eliminates the situation where a single new or modified record can cause a chain reaction in all future insert statements. Running with these options produces larger dumps with no comments so this is probably not something you want to do in production use but for development it should be fine. I've put examples of the commands I use below:
Toad for MySQL has data and schema compare features, and I believe it will even create a synchronization script. Best of all, it's freeware.
I use a piece of software called Navicat to :
- Sync Live databases to my test databases.
- Show differences between the two databases.
It costs money, it's windows and mac only, and it's got a whacky UI, but I like it.
There is a Schema Synchronization Tool in SQLyog (commercial) which generates SQL for synchronizing two databases.
From the feature comparison list.. MySQL Workbench offers Schema Diff and Schema Synchronization in their community edition.
There are many ways certainly, but in my case I prefer the dump and diff command. So here is an script based on Jared's comment:
Feedback is welcome :)
dbSolo, it is paid but this feature might be the one you are looking forhttp://www.dbsolo.com/help/compare.html
It works with Oracle, Microsoft SQL Server, Sybase, DB2, Solid, PostgreSQL, H2 and MySQL
If you only need to compare schemas (not data), and have access to Perl, mysqldiff might work. I've used it because it lets you compare local databases to remote databases (via SSH), so you don't need to bother dumping any data.
It will attempt to generate SQL queries to synchronize two databases, but I don't trust it (or any tool, actually). As far as I know, there's no 100% reliable way to reverse-engineer the changes needed to convert one database schema to another, especially when multiple changes have been made.
For example, if you change only a column's type, an automated tool can easily guess how to recreate that. But if you also move the column, rename it, and add or remove other columns, the best any software package can do is guess at what probably happened. And you may end up losing data.
I'd suggest keeping track of any schema changes you make to the development server, then running those statements by hand on the live server (or rolling them into an upgrade script or migration). It's more tedious, but it'll keep your data safe. And by the time you start allowing end users access to your site, are you really going to be making constant heavy database changes?
check: http://schemasync.org/ the schemasync tool works for me, it is a command line tool works easily in linux command line
There is another open source command-line mysql-diff tool:
There is a useful tool written using perl called Maatkit. It has several database comparison and syncing tools among other things.
SQL Compare by RedGatehttp://www.red-gate.com/products/SQL_Compare/index.htm
DBDeploy to help with database change management in an automated fashionhttp://dbdeploy.com/
For myself, I'd start with dumping both databases and diffing the dumps, but if you want automatically generated merge scripts, you're going to want to get a real tool.
A simple Google search turned up the following tools:
- MySQL Workbench, available in Community (OSS) and Commercial variants.
- Nob Hill database compare, available for free for MySQL.
- A listing of other SQL comparison tools.
Take a look at dbForge Data Compare for MySQL. It's a shareware with 30-days free trial period. It's a fast MySQL GUI tool for data comparison and synchronization, management of data differences, and customizable synchronization.
After hours searching on web for simple tool, i realized i didn't look in Ubuntu Software Center.Here is a free solution i found:http://torasql.com/They claim to have a version for Windows also, but I'm only using it under Ubuntu.
Edit: 2015-Feb-05If you need Windows tool, TOAD is perfect and free:http://software.dell.com/products/toad-for-mysql/
The apache zeta components library is a general purpose library of loosly coupled components for development of applications based on PHP 5.
eZ Components - DatabaseSchema allows you to:
You can check the tutorial here:http://incubator.apache.org/zetacomponents/documentation/trunk/DatabaseSchema/tutorial.html
Very easy to use comparison and sync tool:
Database Comparer http://www.clevercomponents.com/products/dbcomparer/index.asp
Advantages:
- fast
- easy to use
- easy to select changes to apply
Disadvantages:
- does not sync length to tiny ints
- does not sync index names properly
- does not sync comments
I think Navicat for MySQL will be helpful for this case. It supports Data and Structure Synchronization for MySQL.
How To Compare Two Sql Databases
For the first part of the question, I just do a dump of both and diff them. Not sure about mysql, but postgres pg_dump has a command to just dump the schema without the table contents, so you can see if you've changed the schema any.
I'm working with Nob Hill's Marketing team, I wanted to tell you I'll be happy to hear your questions, suggestion or anything else, please feel free to contact me.
We originally decided to create our tool from scratch because while there are other such products on the market, none of them do the job right. It’s quite easy to show you the differences between databases. It’s quite another to actually make one database like the other. Smooth migration, both of schema and data, has always been a challenge. Well, we have achieved it here.
We are so confident that it could provide you a smooth migration, than if it doesn’t – if the migration scripts it generates are not readable enough or won’t work for you, and we can’t fix it in five business days – you will get your own free copy!
Not the answer you're looking for? Browse other questions tagged mysqldatabasediff or ask your own question.
What is a free tool to compare two Sql Server tables (data and schema).
It would be great if the tool can script the differences found.
Free Sql Databases
I also went through some older posts. The closest I have seen is SQLDBDiff but I would love to try more options.
closed as not constructive by KevFeb 25 '13 at 21:01
As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. If this question can be reworded to fit the rules in the help center, please edit the question.
7 Answers
TableDiff.exe should have everything you need. It is one of the hidden gems in SQL Server 2005. So you don't have to download anything.
• Table Difference tool allows you to discover and reconcile differences between a source and destination table or a view. Tablediff Utility can report differences on schema and data. The most popular feature of tablediff is the fact that it can generate a script that you can run on the destination that will reconcile differences between the tables. TableDiff.exe takes 2 sets of input;• Connectivity - Provide source and destination objects and connectivity information.
• Compare Options - Select one of the compare options• Compare schemas: Regular or Strict
• Compare using Rowcounts, Hashes or Column comparisons
• Generate difference scripts with I/U/D statements to synchronize destination to the source. TableDiff was intended for replication but can easily apply to any scenario where you need to compare data and schema.You can find more information about command line utilities and the Tablediff Utility in Books Online for SQL Server 2005.
Even though this has been answered years ago a new comer which works really well is Data Compare within Visual Studio. It is part of Visual Studio 2010 and is part of the Database Professionals GDR update to Visual Studio 2008. This works great when you want to compare the same tables in 2 different databases.
If you need to compare 2 tables with the same schema that are in the same database or with different names TableDiff (mentioned earlier) is a great option.
DBComparer is free and works well for MS SQL Server
We can recommend you a our reliable solutions for data and schema comparison for SQL Server: dbForge Schema Compare for SQL Server and dbForge Data Compare for SQL Server.
Compare Objects In Two Sql Databases
Their main advantages are high speed (up to 3 times quicker than most competitors) and extreme reliability.
Those tools are not free, but you can use 30-days trial for free and you have an opportunity to get a free license for both of the products - please refer to our free license conditions page.
Atlantis Interactiv, now owned by Pragmatic Works has two free tools called Schema Inspector and Data Inspector that works very much like the Red Gates tools.
Try CompareData from Zidsoft. It's free for comparing table/view data and has 30-trial for metadata comparisons
A SQL Server specific database table diff tool is Volpet's Table Diff