SQL Server Comparison Tools

By:   |   Comments (18)   |   Related: 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

Data Comparison

DTS Comparison

  • Red Gate - DTS Package Compare (no longer available)

Server Comparison

  • Quest - ServerCompare (no longer available)

Free Tools

  • SQL Effects CE Edition (no longer available)

If there are other products out there send an email to [email protected] and we will update this list.

Also, check out these tool pages:

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, May 12, 2021 - 9:49:19 PM - SK Back To Top (88676)
"Teratrax Database Compare" Site is no longer available.
Can u pls check and mark it accordingly.

Friday, February 19, 2021 - 4:05:36 PM - Konstantin Semenenkov Back To Top (88267)
MssqlMerge https://db-merge-tools.net/mssqlmerge compares both schema and data. Free version available supporting primary DB programming objects and per-table data diff & merge. I am the author of that tool.

Monday, February 8, 2021 - 11:52:43 AM - Rob Karatzas Back To Top (88198)
How about using the SQL Server Data Tools extension to Visual Studio:

https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15

It allows you to do both data and schema comparisons :)

Thursday, July 2, 2020 - 11:14:55 AM - Kyle Back To Top (86080)

We've been using Red Gate since before this article was first written (2012 I think).  I love it, I can see everything ... data, objects, permissions, users ... everything. 

Friday, January 3, 2020 - 4:44:23 PM - Mounsif Back To Top (83621)

We are using SQLDBDiff for both schema and data it's very fast, cheap and there is a free edition https://www.sqldbtools.com/

Saturday, December 28, 2019 - 2:33:16 PM - Bill Hulsizer Back To Top (83544)

What if the difference between two views, functions, triggers, etc is in a calculation, but the output definition still matches (i.e. same column definitions)?  Can any of these tools detect that difference?  I just wrote a tool that can...

Wednesday, December 18, 2019 - 9:36:22 AM - John X Back To Top (83467)

I'm using ERBuilder Data Modeler and it includes a good comparison feature, you can compare model/model, model to database or database/database.

An HTML comparison report can be generated.

kind regards 

Wednesday, September 11, 2019 - 12:13:17 AM - Ahmed Back To Top (82347)

I am using SQLDBTools for both shema and data it's very cheap and there is a free edition 

https://www.sqldbtools.com/

Best regards

Thursday, August 8, 2019 - 2:00:00 PM - Nilesh Patil Back To Top (82006)

I seldomly needed this & have used visual studio community edition (both 2017 & 2019).

in there there are sql tools (while installing you may need to choose those individual components)

then in visual studion menu 'Tools' > Sql Server you have options for data compare & schema compare.

I don't know how fast/efffective they are compared to other tools but they are free (community edition of VS).

that worked for me.

Friday, June 29, 2018 - 2:54:20 PM - Greg Robidoux Back To Top (76465)

The tools look at the DDL and DML code to figure out what is the same and different.

Friday, June 29, 2018 - 2:45:00 PM - Ernest Back To Top (76464)

 Are the tools only using the schema for comparison??

 

Friday, February 17, 2017 - 7:02:09 AM - Nat Back To Top (46438)

great list of tools, thanks! I use dbForge Schema Compare http://www.devart.com/dbforge/sql/schemacompare/ and am very satisfied!

quickly compare SQL Server databases, analyze differences and perform error-free schema synchronization - this all about dbForge tool!

Monday, September 12, 2016 - 3:48:19 AM - Phil Grayson Back To Top (43301)

Hi, we have a free tool for comparing two database instances of SQL Server including users, plan guides, trace flag, cores, memory, agent jobs etc.

http://aireforge.com/omnicompare/overview.html

Tuesday, August 20, 2013 - 5:03:02 PM - Greg Robidoux Back To Top (26398)

@Binu - have you talked to ApexSQL to see if they have a solution to automate what you are doing with their tool?

Thursday, August 15, 2013 - 12:31:52 PM - Binu Back To Top (26326)

Hi,

I have got a new task for migrating SQL objects from VSS to TFS.

Some objects are there in both VSS and TFS.

Have to compare the objects in both VSS and TFS and find out the differences.

Could you please suggest a way for doing this.

For now, we are scripting out objects from VFS and with the help of APEXDiff tool we are manually comparing every single object with object in TFS.How to automate this?

Thanks in advance.

--Binu

 

 

 

 

Wednesday, May 22, 2013 - 8:50:27 AM - SergeiK Back To Top (24078)

Sorry for the mistake in URL...

Devart's SQL Schema Compare is available here: http://www.devart.com/dbforge/sql/schemacompare/

Wednesday, May 22, 2013 - 8:42:27 AM - SergeiK Back To Top (24076)

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!

Tuesday, November 20, 2012 - 10:18:40 AM - VC Back To Top (20429)

Hi Greg,

 I prefer this one as it can accomodate  

Ref-  http://www.codeproject.com/Articles/205011/SQL-Server-Database-Comparison-Tool 

Cheers,

VC














get free sql tips
agree to terms