How to compare SQL databases in SSMS

ApexSQL Diff, a SQL Server comparison and synchronization tool includes the ability to work from within SQL Server Management Studio. You can compare and synchronize SQL databases directly in SSMS, including backups and versions from source control. This should improve productivity and speed up common tasks.

DBAs sometimes need to compare structures between SQL database tables, or even whole SQL Server databases for that matter. You have executed a stored procedure while you were in SSMS, and you need to check if everything went well. Do you really have to minimize SSMS, launch ApexSQL Diff and start a new project, then set up the source and the destination database while entering the credentials for both? Our SSMS add-in can compare SQL databases directly in just two simple clicks.

In order to install the ApexSQL Diff add-in, during the ApexSQL Diff installation process, an option will be provided to choose the SSMS version to be used. If there are different SSMS versions installed, choice can be made to use the add-in in all or just the ones that are needed.

Once ApexSQL Diff is integrated, open SSMS Object Explorer, right-click the SQL database that needs to be compared, and in the ApexSQL Diff submenu, click Set as source database to select the database as the source or Set as destination database to select the database as the destination.

The ApexSQL Diff tab will be displayed, with the database you selected in the appropriate pane, depending on the selection.

The ApexSQL Diff tab

Use the SSMS Object Explorer as previously described to select the second SQL database that will be used in the SQL comparison process, or use the opposite pane directly.

To switch the source and destination databases, click Reverse.

It is still possible to select different SQL comparison source types. Available choices are the database, backup, script folder, snapshot and source control:

After setting a data source, open the Options tab to define comparison options like ignoring or including an attribute or object during comparison. The SQL database comparison plugin offers a set of predefined choices to compare SQL databases directly in SSMS, which can be accessed directly:

A set of predefined choices to compare SQL databases directly in SSMS

To set options do the following:

  • For application defaults – click ApexSQL defaults
  • For project defaults – click My defaults
  • For your own defaults – click Save as my defaults

To better understand how a selected option affects the comparison, click the “light bulb” tooltip and a new dialog will be displayed showing an explanatory example:

FILLFACTOR attribute

Finally, to start the comparison process, click the Compare button. It will open the ApexSQL Diff standalone application where you can see the comparison results. To create a SQL synchronization script and/or synchronize the SQL databases, continue using the standalone application.

Useful links:

How to: Compare and Synchronize the Data of Two Databases
How to: View Data Differences
Compare and Synchronize Data in One or More Tables with Data in a Reference Database

March 2, 2015