How to check if two SQL Server databases contain the same data?

Question

Given two MS SQL databases that are known to have the same schema, how can I determine if they contain identical copies of the data?

I am using MS SQL Server 2008 Express and coding in the C # and v2.0.Net framework using the ADO.NET API. Two database instances reside on the same SQL server.

Background: I wrote software to export data from a database to a set of files and to re-import data from these files (into another copy of the database); I want to check if I lost any data during the round trip (more precisely, if any data was lost or remained during the initial export).

A rough way, in my opinion, would be SELECT * from each table in each database, and then to compare selected recordsets using client-side code. Is there any other way that would require less client code?

I found the documentation on backing up and restoring the database, as well as on selecting and pasting data, but did not notice this on how to check / prove that the two-way trip was completely successful, that is, how to check two copies of the table in two databases data contains equal data.

Answer 1

The first step is to compare the number of records. You can do it with quick

 select count('x') from TAbleY 

You will need to do this for each table.

To compare the data in the tables, I would use the CHECKSUM function.

Answer 2

RedGate SQL Data Compare might be the answer.

Answer 3

You can use Redgate SQL DATA COMPARE 1> It will show the total amount of data in each table of your database 2> It will generate a script that shows what data is different in it, so by executing it, you can make the data the same. 3> It will show if the schema is different from SQL COMPARE 4> Many functions are useful, you can also synchronize two databases.

Addition to it Visual studio 2012 gives you the opportunity to compare data, you can try it also