question

dvdtknsn avatar image
dvdtknsn asked

Test two databases for data equality

I'm looking for the fastest way of testing two databases for data equality. The use case is automated testing where I take a database, perform an operation on it, and want to compare it with a "known good" baseline database. I know I could use SQL Data Compare, but this feels like overkill, and I'm hoping there's a way of doing it much faster, so it's not prohibitive on huge databases.
testing
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
If the databases are huge the comparison of data will take a lot of time. I have done similar things, though with pretty small datasets. In those cases I have used T-SQL-scripts with some joins, some EXCEPT/INTERCEPT etc. I could also Think if using SSIS to make up a diff between two tables. But I´m pretty sure Redgate's SQL Data Compare is faster. If it's just a small subset of the data you want to compare, then I'd just use T-SQL. No matter how it's done, you WILL make comparison between columns which are not indexed and that is going to be relatively slow.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks. SQL Data Compare is checking every record, which while fine, is as you say, time-consuming. I was hoping that there was some sort of footprint I could compare. I don't care at this stage what has changed, just whether anything has changed.
0 Likes 0 ·
Then I would use Change Data Capture (CDC) on the test database, or possiby Change Tracking (more lightweight than CDC). Look here for an introduction: https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-(cdc)-in-sql-server-2008/
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.