Data difference between two database

Question

Hi All,

I am working on SQL Server 2005. I have took the backup and restore the database. I just wanted to know is there any way I can check whether the data between these two database are diffent. or any script that I can run or check [ could be table by table] to see if any new row added or modified.

EDITED

For example - I have to check the data difference from Database 1 Table_User to Database 2 Table_User.

Thanks

Answer 1

http://www.sql-server-tool.com/

Answer 2

You can use the built-in command line tool TableDiff.exe:

http://msdn.microsoft.com/en-us/library/ms162843(SQL.90).aspx

It performs row by row comparisons along with column-level comparisons. Here's an article that walks through how to use it:

http://www.databasejournal.com/features/mssql/article.php/3594926/SQL-Server-2005-TableDiff-Utility.htm

And another (with a link to a GUI that you can download):

http://weblogs.sqlteam.com/mladenp/archive/2007/03/03/60125.aspx

This would be on a table by table basis - but it's free and already installed for you since you have SQL Server 2005 installed. Hope this helps!

Answer 3

you can use sql compare and sql data compare.(red-gate.com) or you can use sql delta.(sqldelta.com)

Answer 4

I think you want to track what are changes in your restored database comparing to that before it was restored. If so, I hope this may be helpful.

ALTER DATABASE yourdatabasename SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON); GO USE yourdatabasename; GO ALTER TABLE Person.Person ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON); GO