How to verify whether two SQL Server databases contain equal data?

Question

Given two MS SQL databases which are known to have identical schemas, how should I tell whether they contain identical copies of the data?

I'm using MS SQL Server 2008 Express, and coding in C# and v2.0 of the .Net framework, using ADO.NET APIs. The two database instances are both on the same SQL server.

Background: I've written software to export data from a database to a set of files, and, to re-import the data from those files (into another copy of the database); I want to test whether I lost any data during the round-trip (more specifically, whether there's any data lost or left behind during the initial export).

A crude way I guess would be to SELECT * from every table in each database, and then to compare the selected recordsets using client-side code. Is there another way, that would require less client-side code?

I've found documentation about backing up and restoring a database, and about selecting and inserting data, but haven't noticed this about how to verify/prove that a round-trip has been completely successful, i.e. how to verify whether two copies of a table in two databases contain equal data.

Answer 1

First step would be to compare record counts. You can do this with a quick

select count('x') from TAbleY

You would need to do this for every Table.

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

Answer 2

RedGate's SQL Data Compare might be the answer.