Sometimes we need to compare data in two different databases, for example we have two different servers, one for development and one for testing having the same kind of database but the users are different because the test server is used by the testing team and the development server is used by the development team. Now we want to compare the data in the table of those two databases and want to check issues raised by the testing team due to some incorrect data or update one database with the same data in the other database or maybe we are interested in generating a script. So this article will give an idea of how to compare the data of two databases (either in the same server or a different server).
(Note:If you have missed my article about database tracking (it provides an idea of schema comparison as well) check it now http://www.c-sharpcorner.com/UploadFile/31514f/database-tracking-in-sql-through-tfs/).
Procedure to start with data comparison in two different servers
1. Open Visual Studio and go to Tools->SQL Server->New Data Comparison.
![visual stdio tools]()
2. The “New Data Comparison” Window will open.
![new connection]()
3. Click on “New Connection” and select the server name and database name of your source.
![connect to database]()
4. In the same way select your target as well.
![select your target source]()
5. Select all the options using checkboxes and click on "Next".
![checkboxes and click on next]()
6. Click on "Finish".
![Click on finish]()
7. You will see the tables name having columns with different records, only in the source (records that are only in your source database), only in the target (records that are only in your target database), identical records (records exist in both databases means common records).
![Different records]()
8. You can select any table and specific column to see records like I selected the “Company” table with “identical records” columns showing 3 records. In the second screen shot we selected Supplier table with Only in the source columns showing me 5 records.
![select any table and particular column]()
9. If you want to generate scripts you can click on "Generate Scripts" (you can also select tables for which you want to generate scripts).
![generate scripts]()
10. This is giving me insertion scripts because in my target these scripts I need to run to make it the same as the source database.
![insertion scripts]()
11. You can directly update your target as well by clicking on “Update Target”.
![update target]()
12. I Clicked on “Update Target” so both of my databases are the same now (you can select a specific table also to update) so now click on “Select Source/Target”.
![Select Source Target]()
13. Select the source and target and click on "Finish".
![finish connection]()
![no difference in both the database]()
14. Now in both databases only identical records are coming, there is no difference in both of the databases.