Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to upgrade a database from SQL Server 7 and SQL Server 2000 to SQL Server 2008. Additionally I have to be able to prove in a court of law that the data pre-migration is exactly the same post-migration.

Any ideas?

share|improve this question

migrated from stackoverflow.com Feb 13 '12 at 21:39

This question came from our site for professional and enthusiast programmers.

4 Answers 4

up vote 9 down vote accepted

I'd buy Red Gate SQL Data Compare so 100% comparisons are possible.

share|improve this answer
    
Normally I wouldn't vote this up (due to the price mostly), but seeing as how this requires the utmost accuracy, I need to +1 this. –  TheTXI Mar 30 '09 at 10:51

In a court of law (at least the ones I'm familiar with, which is, admittedly, one), you have to be able to prove the source of your proof as well as the proof itself. In other words, demonstrate how you are certain that your information, proving the data are identical, is true.

Unless a third party vendor is willing to stand up in a court of law and testify under oath that their comparison solution is perfect, it won't be a very reliable method, legal-wise.

The best solution in that case is the simplest - write your own script which dumps the data in the tables to a series of text files (i.e., select statements with order by clauses, or post-sorting, to ensure identical order) and compare the two versions of text files. The author of the script (you) will be happy to testify on the accuracy of your script whereas a third-party vendor may not.

Note that the contents of your data tables may not be enough - you may need the contents of system tables to ensure that indexes, stored procedures and so on are converted correctly.

As always, legal advice on the web is worth every cent you paid for it, so you should consult a real lawyer if it's as important as you indicate.

share|improve this answer
    
I would say, combine this answer with the one of Lennart: You do not need full dumps to compare. You could split up your dumps into smaller ones (per table/per year) and only store the MD5 checksums to compare. –  GvS Mar 30 '09 at 11:20
    
@GvS, I don't believe MD5 checksums would add anything to this method. This method already detects with perfect accuracy if the data has or hasn't changed, and MD5s on their own would not be as reliable a way due to the possibility, however small, of an identical hash value from different data. –  paxdiablo Mar 30 '09 at 12:07

MD5 checksum of full dump?!

share|improve this answer
    
This could fail based on the way the differing system would dump the data, but if it did match then could be used to say they are the same. However, given the volume of data, two differing sets could produce the same hash because of the low number of hash potentials compared to the data size –  cjk Mar 30 '09 at 11:14

Record the total row count for all tables before migration or upgrade, and compare these counts after migration.

share|improve this answer
2  
A COUNT doesn't guarantee the same data, only the same number of rows. –  gbn Feb 13 '12 at 16:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.