4

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.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
ChrisW
  • 54,973
  • 13
  • 116
  • 224

3 Answers3

1

You can use Redgate SQL DATA COMPARE 1>It will tell total count of data in each table of your database 2> It will generate a script which will show what data is different in it so that by executing it you can make data same. 3>It will show if schema is different by SQL COMPARE 4>Lots of features are useful you can also sync two database.

Addition to it Visual studio 2012 gives you an option to compare the data ,You can try that also

mimix
  • 29
  • 3
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.

ChrisW
  • 54,973
  • 13
  • 116
  • 224
Jeff
  • 5,913
  • 2
  • 28
  • 30
  • Thanks; I see now that there's also a CHECKSUM_AGG function which can be used to take a checksum over every row in a table. http://dbwhisperer.blogspot.com/2009/02/checksumagg-very-nifty-function.html suggests combining CHECKSUM with CHECKSUM_AGG, for QA applications. – ChrisW May 20 '09 at 01:46
1

RedGate's SQL Data Compare might be the answer.

Kev
  • 118,037
  • 53
  • 300
  • 385
  • That product has more functionality than I'm asking for, but its existence suggests that perhaps even the basic functionality isn't built-in. – ChrisW May 20 '09 at 01:44