Checking if 2 tables ( on different servers ) have the same exact data

So it seems that the company who is hosting our SQL Servers has been having some trouble with the replication from the production server to the backup server... I believe some of the tables have been replicating correctly though. Replication is done daily (after hours).

Is there a way I can compare 2 of the same tables, 1 from the backup vs 1 from the production server to see if last nights replication worked?

The only way I could find was to run the following query on both servers and seeing if the result matched which "might" mean that to 2 tables contain the same information.

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
        FROM   (

                    SELECT  * 
                    FROM    table_to_compare
               ) t1

using the above code, It seems that the table did replicate successfully as the checksum values are the same but I'm not sure how reliable this method is.

Does anyone know of a better method to check this out or if this is a good way?

I'm running SQL Server 2008 on a windows server 2008 computer.

Thanks.

I believe you're looking for the tool tablediff which lets you do exactly that - compare two replicated tables for differences. You might find this article useful, to get started.

Here is a GUI for tablediff

StackExchange Database Administrators Q#22385, answer score: 11