Earlier I wrote about An Efficiency Tool to Compare and Synchronize SQL Server Databases and it was very much well received. Since the blog post I have received quite a many question that just like data how we can also compare schema and synchronize it. If you think about comparing the schema manually, it is almost impossible to do so. Table Schema has been just one of the concept but if you really want the all the schema of the database (triggers, views, stored procedure and everything else) it is just impossible task.

If you are developer or database administrator who works in the production environment than you know that there are so many different occasions when we have to compare schema of the database. Before deploying any changes to the production server, I personally like to make note of the every single schema change and document it so in case of any issue , I can always go back and refer my documentation. As discussed earlier it is absolutely impossible to do this task without the help of third party tools. I personally use Devart Schema Compare for this task. This is an extremely easy tool.

Let us see how it works. First I have two different databases – a) AdventureWorks2012 and b) AdventureWorks2012-V1. There are total three changes between these databases. Here is the list of the same.

• One of the table has additional column
• One of the table have new index
• One of the stored procedure is changed

Now let see how dbForge Schema Compare works in this scenario.

First open dbForge Schema Compare studio. Click on New Schema Comparison.

It will bring you to following screen where we have to configure the database needed to configure. I have selected AdventureWorks2012 and AdventureWorks-V1 databases.

In the next screen we can verify various options but for this demonstration we will keep it as it is.

We will not change anything in schema mapping screen as in our case it is not required but generically if you are comparing across schema you may need this.

This is the most important screen as on this screen we select which kind of object we want to compare. You can see the options which are available to select. The screen lets you select the objects from SQL Server 2000 to SQL Server 2012.

Once you click on compare in previous screen it will bring you to this screen, which will essentially display the comparative difference between two of the databases which we had selected in earlier screen. As mentioned above there are three different changes in the database and the same has been listed over here. Two of the changes belongs to the tables and one changes belong to the procedure. Let us click each of them one by one to see what is the difference between them.

In very first option we can see that there is an additional column in another database which did not exist earlier.

In this example we can see that AdventureWorks2012 database have an additional index.

Following example is very interesting as in this case, we have changed the definition of the stored procedure and the result pan contains the same.

dbForget Schema Compare very effectively identify the changes in schema and lists them neatly to developers. Here is one more screen. This software not only compares the schema but also provides the options to update or drop them as per the choice. I think this is brilliant option.

Well, I have been using schema compare for quite a while and have found it very useful. Here are few of the things which dbForge Schema Compare can do for developers and DBAs.

• Compare and synchronize SQL Server database schemas
• Compare schemas of live database and SQL Server backup
• Generate comparison reports in Excel and HTML formats
• Eliminate mistakes in schema changes propagation across environments
• Track production database changes and customizations
• Automate migration of schema changes using command line interface

I suggest that you try out dbForge Schema Compare and let me know what you think of this product.

Reference: Pinal Dave (http://blog.sqlauthority.com)