SQL SERVER – An Efficiency Tool to Compare and Synchronize SQL Server Databases

There is no need to reinvent the wheel if it is already invented and if the wheel is already available at ease, there is no need to wait to grab it. Here is the similar situation. I came across a very interesting situation and I had to look for an efficient tool which can make my life easier and solve my business problem.

Here is the scenario. One of the developers had deleted few rows from the very important mapping table of our development server (thankfully, it was not the production server). Though it was a development server, the entire development team had to stop working as the application started to crash on every page. Think about the lost of manpower and efficiency which we started to loose.  Pretty much every department had to stop working as our internal development application stopped working. Thankfully, we even take a backup of our development server and we had access to full backup of the entire database at 6 AM morning. We do not take as a frequent backup of development server as production server (naturally!).

Even though we had a full backup, the solution was not to restore the database. Think about it, there were plenty of the other operations since the last good full backup and if we restore a full backup, we will pretty much overwrite on the top of the work done by developers since morning. Now, as restoring the full backup was not an option we decided to restore the same database on another server. Once we had restored our database to another server, the challenge was to compare the table from where the database was deleted. The mapping table from where the data were deleted contained over 5000 rows and it was humanly impossible to compare both the tables manually. Finally we decided to use efficiency tool dbForge Data Compare for SQL Server from DevArt. dbForge Data Compare for SQL Server is a powerful, fast and easy to use SQL compare tool, capable of using native SQL Server backups as metadata source. (FYI we Downloaded dbForge Data Compare)

Once we discovered the product, we immediately downloaded the product and installed on our development server. After we installed the product, we were greeted with the following screen.

datacomp1 SQL SERVER   An Efficiency Tool to Compare and Synchronize SQL Server Databases

We clicked on the New Data Comparision to start our new comparison project. It brought up following screen.

datacomp2 SQL SERVER   An Efficiency Tool to Compare and Synchronize SQL Server Databases

Here is the best part of the product, we just had to enter our database connection username and password along with source and destination details and we are done. The entire process is very simple and self intuiting.

datacomp3 SQL SERVER   An Efficiency Tool to Compare and Synchronize SQL Server Databases

The best part was that for the source, we can either select database or even backup. This was indeed fantastic feature. Think about this, if you have a very big database, it will take long time to restore on the server. Once it is restored, you will be able to work with it. However, when you are working with dbForge Data Compare it will accept database backup as your source or destination.

datacomp4 SQL SERVER   An Efficiency Tool to Compare and Synchronize SQL Server Databases

Once I click on the execute it brought up following screen where it displayed an excellent summary of the data compare. It has dedicated tabs for the what is changing in what table as well had details of the changed data. The best part is that, once we had reviewed the change. We click on the Synchronize button in the menu bar and it brought up following screen.

datacomp5 SQL SERVER   An Efficiency Tool to Compare and Synchronize SQL Server Databases

You can see that the screen has very simple straight forward but very powerful features. You can generate a script to synchronize from target to source or even from source to target. Additionally, the database is a very complicated world and there are extensive options to configure various database options on the next screen. We also have the option to either generate script or directly execute the script to target server. I like to play on the safe side and I generated the script for my synchronization and later on after review I deployed the scripts on the server.

datacomp6 SQL SERVER   An Efficiency Tool to Compare and Synchronize SQL Server Databases

Well, my team and we were able to get going from our disaster in less than 10 minutes. There were few people in our team were indeed disappointed as they were thinking of going home early that day but in less than 10 minutes they had to get back to work.

There are so many other features in  dbForge Data Compare for SQL Server, I am already planning to make this product company wide recommended product for Data Compare tool. Hats off to the team who have build this product.

Here are few of the features salient features of the dbForge Data Compare for SQL Server

  • Perform SQL Server database comparison to detect changes
  • Compare SQL Server backups with live databases
  • Analyze data differences between two databases
  • Synchronize two databases that went out of sync
  • Restore data of a particular table from the backup
  • Generate data comparison reports in Excel and HTML formats
  • Copy look-up data from development database to production
  • Automate routine data synchronization tasks with command-line interface

Go Ahead and Download the dbForge Data Compare for SQL Server right away. It is always a good idea to get familiar with the important tools before hand instead of learning it under pressure of disaster.

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

6 thoughts on “SQL SERVER – An Efficiency Tool to Compare and Synchronize SQL Server Databases

  1. I’ve tried the program and it’s fantastic. The only thing you must take care of is to put primary key on every table – without primary key dbForge can not compare tables and tell which rows are different from others.


  2. Pingback: SQL SERVER – How to Compare the Schema of Two Databases with Schema Compare | Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL Contest – USD 100 Gift Card and Attractive Discount from Devart | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s