Diff Utility in SQL Server?

[ekta22]

I am using SQL Sever 2000 to replicate between 2 databases. There seems to be some discrepancy in the data on the 2 databases. I was wondering if there is some utility in SQL Server which I can use to see the difference between the two databases and fix it.

Thanks

 

[Catadmin]

Not in SQL itself, but there are plenty of tools out there. Google SQL Compare and you'll come up with a whole list of tools. Some of them are demos, some of them shareware and some of them purchasable.

We use Red Gate's tools and they're pretty good.





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[katgirl]

AdeptSQL is another very good tool and is compatible with 2000 and 2005

and it works very well, I have no complaints

whereas I've had issues with other products hanging

 

[SQLBill]

I somehow got signed up with Edgewood Solutions for their MSSQLTips email (I think it was from the Dallas PASS conference) and got an email about checking differences in databases. Here it is:

SQL Server Comparison Tools


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
e-Dule - DB SynchroComp
PrimeLogics - DataVision 2007
Quest – SchemaCompare
Red Gate – SQL Compare
SQL Effects Clarity
TASC - SQL Delta
Teratrax Database Compare
TulsaSoft - SQL Examiner
Voltex Data Systems - SQLDBcontrol
XpressApps - sqlXpress Diff
xSQL Software - xSQL Object
Data Comparison

ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
Quest - DataCompare
Red Gate – Data Compare
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 Clarity 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.

There were links to all those tools, but for some reason I can't get them to copy over. So just do a search for them.

-SQLBill

Posting advice: FAQ481-4875