What Is Best Tool to Compare Two SQL Server Databases (Schema and Data)

What is best tool to compare two SQL Server databases (schema and data)?

I am using Red-Gate's software:
http://www.red-gate.com

What is a free tool to compare two SQL Server Databases?

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.

How can I compare 2 sql server databases schema and get the update script

This one is really popular, but it's not free.

Apex SQL

If your using SQL Server 2005 or 2008. You can try these

SQL Server Database Comparison Tool

DB Comparer

You can also use the Schema Compare option in SSMS.

T-SQL - Is there a (free) way to compare data in two tables?

In very simple cases you will be able to define in your local SQL Server a new Linked Server (see in Microsoft SQL Server Management Studio under "Server Objects" / "Linked Server") and use INNER JOIN and OUTER JOIN to find out differences between A and B tables.

In a real and more complex situation you should take into consideration Referential Integrity, different Foreign Keys, and Identity (auto incremental) fields existing in the destination database, so the update script will be more complex. So I'll recommend you don't spend your time in creating synchronization of your developer and production DB and use a standard tool instead. I use for example the features of Visual Studio Team Edition 2008 (or Database edition) or Visual Studio 2010 Ultimate edition to compare data in two databases. It works very well.

what is the best tool to compare SQL Server database schemas across different db servers

The best tool I've come across for doing this is SQLCompare from RedGate. I've never needed to use it to compare at the schema level though.

What is best tool to compare two different Database ,the first database is Ms Sql and another is DB2

I think IBM Data Studio can do that. I don't know if it's the best tool, as you didn't say how you'd define "best".

---

Related Topics