Comparing SQL Server Databases
In the March 2004 MSDN magazine, someone asked a question in the Web Q&A section
that I really needed the answer to, and I didn’t even know I needed it.
Q: How do I design a script to create an automated comparison of two databases, mainly
to detect possible changes in structure, keys, indexes, table names, and so on? Do
you have any advice on how to achieve this with VBScript or some other means?
A: EXEC sp_table_validation
@Table = ‘Authors’,
@rowcount_only = 2, — rowcount and checksum
@full_or_fast = 2 — fast count
answer is here and is the 2nd question down.
As if this weren’t enough, we went the next step and purchased RedGate’s
SQL Data Compare. My life will never be the same. As a content company, we deal
with MANY copies of content DBs and are constantly wanting to see what changed from
one revision to another. Not only is there a slick visual comparison tool in here,
but there is a .Net API that allows us to automate QC measures by doing data comparisons.
We can even see the differences in the XML that we store as nText.