SQL Server Comparison Tools

By: Greg Robidoux   |   Read Comments (3)   |   Related Tips: More > DBA Best Practices

Problem

There is often the need to compare both data and database structures from two databases either on the same server or on different servers.  Most text editors have a built-in process to allow you to do a diff and identify any differences between the two files, but comparing data is not quite that easy.  Using this diff process is great if you only want to compare a few files, but what if you need to scan your entire database to look for differences?

As with anything you can always create your own application to automate the comparison process.  You could cursor through all of the tables read the T-SQL from one server, get the T-SQL for the same object from the other server and do a comparison.   To compare the data you can write scripts that read row by row and do a comparison of the data.  But why bother when there are so many products out there already.

Solution

Luckily a lot of products already exist.  These products allow you to compare database objects, data, DTS scripts, servers, etc...  In addition, these products are not all that expensive either.  Some of the products are under $100 US and I also found a company that offers a free version, see below.

 Following is a list of the various products that allow you to do comparisons:

Object Comparison:

• AdeptSQL Diff
• AlfaAlfa Software - SQL Server Comparison Tool
• ApexSQL – SQL Diff
• Best SoftTool – SQL DBCompare
• Devart - dbForge Schema Compare for SQL Server
• e-Dule - DB SynchroComp
• Innovartis - DB Ghost Schema Compare
• PrimeLogics - DataVision 2007 (no longer available)
• Quest – SchemaCompare
• Red Gate – SQL Compare
• SQL Effects
• SQL Server Database Comparison Tool
• TASC - SQL Delta
• Teratrax Database Compare
• TulsaSoft - SQL Examiner
• Voltex Data Systems - SQLDBcontrol  
• XpressApps - sqlXpress Diff  (no longer available)
• xSQL Software - Schema Compare

Data Comparison

• ApexSQL – SQL Diff
• Best SoftTool – SQL DBCompare
• Devart - dbForge Data Compare for SQL Server
• Innovartis - DB Ghost Data Compare
• Quest - DataCompare
• Red Gate –  Data Compare
• SqlDiffFramework
• TASC - SQL Delta
• TulsaSoft - SQL Data Examiner
• xSQL Software - xSQL DataCompare

DTS Comparison

• Red Gate – DTS Package Compare

Server Comparison

• Quest - ServerCompare

Free Tools

• SQL Effects CE Edition

As far as I can tell this is a complete list, but if there are other products out there send an email to tips@mssqltips.com and we will update this list.

Next Steps

• Next time you need to compare your database objects don't waste your time doing it manually.  Purchase one of these products or download the free version and give it a try.  Most if not all of these vendors offer free trial versions.
• Determine what needs to be compared and how frequently.  Look at the different options that allow you to compare objects, data, dts packages and even servers.
• We provided the list of products, so take this list and find the product that is right for you.

Updated: 12/17/2010

Last Update: 9/11/2006

About the author

Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Comments and Feedback:

Tuesday, November 20, 2012 - 10:18:40 AM - VC	Read The Tip
Hi Greg,  I prefer this one as it can accomodate   Ref-  http://www.codeproject.com/Articles/205011/SQL-Server-Database-Comparison-Tool  Cheers, VC

Wednesday, May 22, 2013 - 8:42:27 AM - SergeiK

Read The Tip

I wonder why Devart's products are not mentioned here. I'm sure they must be observed here. Try dbForge Data and Schema Compare tools – the most professional sql comparison tools I've ever used! SQL Data Compare: http://www.devart.com/dbforge/sql/datacompare/ SQL Schema Compare: http://www.devart.com/dbforge/mysql/schemacompare/ The price is low but the quality is great! p.s. Free trials are available!

Wednesday, May 22, 2013 - 8:50:27 AM - SergeiK	Read The Tip
Sorry for the mistake in URL... Devart's SQL Schema Compare is available here: http://www.devart.com/dbforge/sql/schemacompare/

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement. SQL Developer Bundle: Cut out dull work with 12 tools for simpler, faster database development. Free trial Is “blocking” a bad word at your company? Contact the Edgewood SQL Server Consultants for the resolution. Spring Clean Your Data - Clean your global contact data with Melissa Data tools for SSIS. Download a free trial! Demystify TempDB Performance and Manageability