Compare and Synchronize SQL Server Databases

Why compare databases?

Whether you are a developer that uses SQL Server on the back end to support your application or a database administrator in charge of monitoring and maintaining those SQL Server databases you often have to deal with different versions of the same database. For example, you may have a development environment, a testing or QA environment and a production environment and at any given time your database is in different "states" in each of those environment. As you work on implementing changes and additions to your applications you routinely make changes on the development database like adding tables, adding columns, changing types, creating indexes, adding new and modifying existing views, stored procedures, user defined functions etc.

At different points in the development you may need to promote all the work you have done on to the QA environment so that the QA team can start "pocking holes" in the work you have done. As soon as you do that you go back to your development World and continue working on your application and making database changes as the need arises so, it won't be long before the version of the database you are working on does not match the version the QA team is testing and that does not match the version of the SQL Server database that is running on your production servers.

Any professional in this position will keep meticulous records on each version of the database so that at any given time he would be able to tell what is different from one version to the other. That's well and good but it does not take much to realize that, especially if the database is relatively big and changes relatively many, sorting through such records to figure out what changes when (let alone how to change it back if necessary) is like looking for the needle in the haystack. That's why the need to have a tool that compares the SQL Server databases and tells you what is different.
 
How do I compare two databases?

xSQL Object which you can download from this site allows you to compare the schemas of two SQL Server databases with a few clicks, regardless of the location of those databases (as long as you can access those databases). The results of the comparison are displayed on an easy to use grid that shows the objects on the top section and the differences for the selected object on the bottom section. In addition of the side by side display of the selecte objects' scripts it also provides two change scripts - one to make the object on the second database in the comparison the same as the corresponding object in the first database in the comparison and abother change script to go the other way.
 
What do I do after comparing the two databases?

After you have compared the SQL Server databases in question, knowing on which direction you wish to transfer changes (example: if you were comparing the development version of your SQL Server database with the QA version it is likely that you would want to transfer changes from the development version to the QA version) you simply click on the right button (link) and xSQL Object automatically generates the change script required to transfer those changes. The script generated is safe and version specific (this is great for cases when you have different versions of SQL Server running on different environments - for example: you have upgraded the development environment to SQL Server 2005 or SQL Server 2008 but your QA and production environments are still on SQL Server 2000). At this point you can simply review the script to ensure that you do indeed intend to make those changes on the target database and then execute the script directly from xSQL Object's interface.
 
How about the data - the content of two SQL Server databases?

So, what if you have some sort of a distributed SQL Server database or a replicated database or some other scenario where you need to compare the actual data between two SQL Server databases to make sure that the replication has worked correctly or to see what records were changes since the last backup if you are trying to perform some auditing or for some other reason? Well, we thought of that to: xSQL Data Compare allows you to compare the data, the content, of two SQL Server databases, see where the differences are and then transfer all or some of those changes from one database to the other. xSQL Data Compare comes together with xSQL Object and the corresponding command line utilities as part of the xSQL Bundle that you can download from this site.

How much am I going to have to pay for those tools?

Not much at all - in fact it is likely that you may not have to pay anything - xSQL Bundle which includes both xSQL Object and xSQL Data Compare is completely free for SQL Server Express and comes in a free Lite Edition for other editions of SQL Server.