Anyone know of any good Database Diff tools?

Question

I can't find any good ones in sourceforge :( Anyone has any success with open source (or retail) database diff tool?

EDIT: for sqlserver 2005

Answer 1

Red-Gate products are the best, if it's a Sql Server we are talking about. (why does that sound like an advert... sorry)

Answer 2

What database?

Red Gate has some good (commercial) products for SQL Server.

Answer 3

Redgate SQL Compare and SQL Data Compare are awesome

Answer 4

As far as I'm concerend you cannot beat Red-Gate SQL compare. They have a 14 day trial so you can check out what the product can do. They also have a data compare product that will compare the data contents.

Answer 5

I use red gate sql bundle. Its very good, you can compare schema and data as well and generate diff scripts or sync the databases directly from the tool itself. With the pro version you can even automate the tasks.

Answer 6

It really matters what database we are talking about here. There are different tools for each one.

There are tools SQLDiff and the Red Gate products which have already been mentioned.

Answer 7

asked already http://stackoverflow.com/questions/10688/best-tool-for-auto-generating-sql-change-scripts#10768

Answer 8

Do you need to diff data or the structure? To diff data we use EMS Data Comparer and we are pretty happy about it. Their new version is very useful and they have version for different kinds of databases.

EMS also has a diff tool for structure but I did not like it as much. Then again that was ~ 2 years ago and they had put out more than couple new versions since.

PS. EMS is not free or open source.

Answer 9

If you are looking for a way to compare the data in 2 tables in 2 different databases, then MS has a nice free hidden tool that will do the trick. It will create the necessary .sql script to do the update/insert/delete.

The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the following tasks:

* A row by row comparison between a source table in an instance of Microsoft SQLServer acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
* Perform a fast comparison by only comparing row counts and schema.
* Perform column-level comparisons.
* Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
* Log results to an output file or into a table in the destination database.

Syntax

tablediff [ -? ] | { -sourceserver source_server_name[\instance_name] -sourcedatabase source_database -sourcetable source_table_name [ -sourceschema source_schema_name ] [ -sourcepassword source_password ] [ -sourceuser source_login ] [ -sourcelocked ] -destinationserver destination_server_name[\instance_name] -destinationdatabase subscription_database -destinationtable destination_table [ -destinationschema destination_schema_name ] [ -destinationpassword destination_password ] [ -destinationuser destination_login ] [ -destinationlocked ] [ -b large_object_bytes ] [ -bf number_of_statements ] [ -c ] [ -dt ] [ -et table_name ] [ -f [ file_name ] ] [ -o output_file_name ] [ -q ] [ -rc number_of_retries ] [ -ri retry_interval ] [ -strict ] [ -t connection_timeouts ] }

Arguments

[ -? ]

Returns the list of supported parameters.

-sourceserver source_server_name[\instance_name]

Is the name of the source server. Specify source_server_name for the default instance of SQL Server. Specify source_server_name\instance_name for a named instance of SQL Server.

-sourcedatabase source_database

Is the name of the source database.

-sourcetable source_table_name

Is the name of the source table being checked.

-sourceschema source_schema_name

The schema owner of the source table. By default, the table owner is assumed to be dbo.

-sourcepassword source_password

Is the password for the login used to connect to the source server using SQL Server Authentication.
Security Note:
When possible, supply security credentials at runtime. If you must store credentials in a script file, you should secure the file to prevent unauthorized access.

-sourceuser source_login

Is the login used to connect to the source server using SQL Server Authentication. If source_login is not supplied, then Windows Authentication is used when connecting to the source server. When possible, use Windows Authentication.

-sourcelocked

The source table is locked during the comparison using the TABLOCK and HOLDLOCK table hints.

-destinationserver destination_server_name[\instance_name]

Is the name of the destination server. Specify destination_server_name for the default instance of SQL Server. Specify destination_server_name\instance_name for a named instance of SQL Server.

-destinationdatabase subscription_database

Is the name of the destination database.

-destinationtable destination_table

Is the name of the destination table.

-destinationschema destination_schema_name

The schema owner of the destination table. By default, the table owner is assumed to be dbo.

-destinationpassword destination_password

Is the password for the login used to connect to the destination server using SQL Server Authentication.
Security Note:
When possible, supply security credentials at runtime. If you must store credentials in a script file, you should secure the file to prevent unauthorized access.

-destinationuser destination_login

Is the login used to connect to the destination server using SQL Server Authentication. If destination_login is not supplied, then Windows Authentication is used when connecting to the server. When possible, use Windows Authentication.

-destinationlocked

The destination table is locked during the comparison using the TABLOCK and HOLDLOCK table hints.

-b large_object_bytes

Is the number of bytes to compare for large object data type columns, which includes: text, ntext, image, varchar(max), nvarchar(max) and varbinary(max). large_object_bytes defaults to the size of the column. Any data above large_object_bytes will not be compared.

-bf number_of_statements

Is the number of Transact-SQL statements to write to the current Transact-SQL script file when the -f option is used. When the number of Transact-SQL statements exceeds number_of_statements, a new Transact-SQL script file is created.

-c

Compare column-level differences.

-dt

Drop the result table specified by table_name, if the table already exists.

-et table_name

Specifies the name of the result table to create. If this table already exists, -DT must be used or the operation will fail.

-f [ file_name ]

Generates a Transact-SQL script to bring the table at the destination server into convergence with the table at the source server. You can optionally specify a name and path for the generated Transact-SQL script file. If file_name is not specified, the Transact-SQL script file is generated in the directory where the utility runs.

-o output_file_name

Is the full name and path of the output file.

-q

Perform a fast comparison by only comparing row counts and schema.

-rc number_of_retries

Number of times that the utility retries a failed operation.

-ri retry_interval

Interval, in seconds, to wait between retries.

-strict

Source and destination schema are strictly compared.

-t connection_timeouts

Sets the connection timeout period, in seconds, for connections to the source server and destination server.

Return Value

Answer 10

For SQL Server, it is built into Visual Studio Team System 2008 Database Edition, aka DataDude

Answer 11

Are you comparing database schemas?

For Oracle, I have always found it easy to use 'TOAD' when comparing 'schemas' (not the data)

Answer 12

REDGATE SqlCompare FTW! They'll work with you on personal pricing and they're a great group to work with. I dont even think about my sql builds anymore. I run the compare and then let the sync wizard do its thing. Its nice having that level of comfort when i need to push db changes. Definitely go redgate.

Answer 13

StarInix Free Database Compare

It is a free, fairly decent database diff tool. It does do a good job of showing the differences between db objects like tables, stored procedures, views, etc.

The databases it supports for diffing are SQL Server, MySQL, and MS Access (but not Oracle.) One feature it does lack is the functionality to generate the sql scripts that represent the diff. Nonetheless, it still is a handy tool for quick and dirty situations if unable to spend $$ on some of the more commercial products available out there.

Answer 14

There is also CompareDB
if you have A LOT of time you can do this by using the SystemViews in MS SQL 2005 and compare them both. But again this would be a looong story.

Answer 15

You have also the Data compare tool thats good for SQL as well as MySQL.

Answer 16

I was wondering about this too. I've been doing this somewhat differently, just using standard tools like diff and mysqldump. Granted, mysqldump is only a standard tool if you're using MySQL... but I gather there are similar programs available for other DBMS, and/or ways to convert those databases to mysql.

My method works well enough, but I thought it would nice to find out if there was an actual tool for this that's free. I guess not?

Anyway what follows is a basic gist of what I do to compare entire databases. I don't really do things exactly this way -- for example I usually use git diff -- and I didn't test this code, so some of the commands may be missing options, or have the wrong ones.

Overview

• make a directory for each of the databases that you want to compare, eg $dir1 and $dir2
• dump the schema and data for each table of each database into that DB's directory
• diff the contents of the directories

Dumping

• In each of these directories you're going to create a per-table database dump. You want there to be one file for the schema of each table, and one file for the data that's in each table. So for if each database contains 20 tables, you end up with 40 files in each directory. There are some things to keep in mind:
  • you want to avoid having the dumps contain comments and such, because this will sometimes introduce differences that aren't actually part of the dataset. with mysqldump you pass the --skip-comments option to do this.
  • since you're going to be diffing on a line-by-line basis, you need to make sure that each insert statement only contains one row of the DB table. --skip-extended-insert will do this with mysqldump.
  • if the dumps are in random order you're going to end up with a lot of fake changes that are actually just rearrangements of the same data.
  • If your tables have sortable primary keys, you can pass the --order-by-primary option to mysqldump to tell it to sort the resulting dumps by table primary keys.
  • If your tables don't have primary keys, you can just sort the lines of the data dumps that contain insert statements for the same effect.
  • mysqldump's --no-data and --no-create-info options will, respectively, create schema-only and data-only dumps

Diffing

• Once you have the dump files you can do something like $(diff -rq $dir1 $dir2) to get a list of files that differ, and then call diff -up again for each pair of files. I use these commands in a script to build up a single text file in the form

  table_name
    INSERT INTO `table_name` VALUES ('data which is the same in both db\'s');
    -INSERT INTO `table_name` VALUES ('unique to the first DB');
    +INSERT INTO `table_name` VALUES ('unique to the the second DB');
    INSERT INTO `table_name` VALUES ('data which is the same in both db\'s');
  second_table_name
    ...
  

  • the script is something like (in pseudo-bash-code)

    for file in "${differing_files[@]}" do
      echo "$file";
      diff -up "$dir1/$file" "$dir2/$file" | sed 's/^/  ';
    done
    

  • I pipe the script output into vim, and use :set foldmethod=indent to make everything presentable: I get a list of tables and can open the folds to see the differences for that table's data or definition.
  • Alternatively there are various free tools available to explore the differences between two directories' contents. These will typically present convenient side-by-side diffs of the files, and/or make it more obvious which characters of differing lines are different.

Answer 17

There are a couple of really nice freeware GUI tools that can be found at http://www.xsqlsoftware.com/ - xSQL Object and xSQL Data Compare, there are 'lite' versions of these tools available which I have used in the past and found that they fulfilled my needs very well.

Answer 18

Hi there,

You might want to give a try to Volpet's Table Diff:

http://www.volpet.com/

You can try a fully-functional copy for 30 days.

Please let me know for anything.

Thank you,

Giammarco

Answer 19

seriously check out SQL Examiner.

I swear by it. It's up there with Visual Studio and Management Studio as my essential .NET development tools.

Answer 20

Sql Delta. I used to use Red Gate, but their products are very heavy, and somewhat expensive. Sql Delta is here. I've been using this product now for over a year, and love it.

Answer 21

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)

Answer 22

Check out SQL Examiner Suite. The tool compares (and synchronizes) both schema and data. I use SQL Examiner for keeping database schema under version control.

Answer 23

SqlDiffFramework is a visual diff program for data sources (rather than files like your typical diff program). As the name implies, it is a framework: you have to feed it two queries, one for each system you wish to compare. If you are comparing what are presumably identical DBs you would feed the same query to both. But the power of SqlDiffFramework comes in that you can compare result sets from 2 disparate data sources: SQL Server, Oracle, MySql, or any ODBC data source (e.g. Access, Excel, ODBC, etc.). To my knowledge this makes it unique among all the tools out there.

SqlDiffFramework provides two side-by-side query tools in one window. You simply point one side to each database, then run both queries to fetch and compare the data. (You can even save snapshots and then run a diff on the snapshots of your choice to see how differences, if any, emerge over time.)

SqlDiffFramework compares only data, not schemas. Also it does not generate scripts to make them match.

[I introduced SqlDiffFramework in April, 2010; it is open-source and freely available.]

Answer 24

i use Red Gate for doing the schema & data comparisons. It is very powerful and user friendly.

Answer 25

Well, you definitely should try our solutions: dbForge Schema Compare for SQL Server and dbForge Data Compare for SQL Server.

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.