How to compare schemas of two databases in SQL Server

Learn how easy it is to compare two SQL Server databases and synchronize them using dbForge Schema Compare for SQL Server.

Getting started

When you run dbForge Schema Compare for SQL Server, the tool automatically opens the Start page with a list of your recent comparison projects with a .scomp extension which you can open with a click. On the Start page, you can do the following:

  • Launch a new schema comparison by clicking New Schema Comparison in the upper-right corner
  • Import the previously created data comparison project by clicking Open Schema Comparison Project in the upper-right corner
Getting started with Schema Compare for SQL Server

Setting up connection

Prior to launching a new schema comparison, you need to establish a new database connection. To set up the SQL Server connection:

  1. On the Database menu, select New Connection.
  2. In the Database Connection Properties that opens, specify the connection properties you want to add and click Connect.

Setting up connection

Setting data sources for comparison

After we established the SQL Server database connection, we can start a new comparison project and specify two data sources to be compared.

Target is the database that is updated (synchronized) with the results of schema comparison to make it the same as Source. In dbForge Schema Compare for SQL Server, you can compare the following Source and Target types: Database, Backup, Scripts Folder, Source control, and Snapshot.

To select the source and target databases for comparison, do the following:

  1. On the Start page, click New Schema Comparison.
  2. In the New Schema Comparison wizard that opens, specify the source and target comparison details on the Source and Target tab:
    • Select the Database as a type
    • Choose the connection from the Connection drop-down list
    • Select the databases you want to compare
  3. Click Next to select options for schema comparison.

If you want to swap the source and target, click Swap the Source and Target Settings in the New Schema Comparison wizard Swap Source and Target settings.

Selecting Source and Target for comparison

Configuring comparison options

After the source and target connections and databases were set, you can customize the default comparison behavior, for example, ignore some objects or properties. On the Options wizard tab, select the comparison options and then click Next. For more information about the options in the New Schema Comparison wizard, see Setting comparison options.

If you want to save the comparison settings to a command-line arguments file, click the link in the upper-right corner Save settings to a command-line arguments file. Later you can use this file for automatic comparison and synchronization through the command line.

To save the current selection of the options as your defaults, click Save As My Defaults.

Configuring comparison options

Custom schema mapping

If you need to map schemas (with different names, one-to-many or many-to-one relationships), switch to the Schema Mapping wizard tab. Select the source and target schemas, and click Compare – dbForge Schema Compare for SQL Server will automatically map the schema objects.

Custom schema mapping

Analyzing comparison results

The Schema Comparison window displays the .scomp file represented as a grid with detailed information about the objects grouped by status (Only in Source, Only in Target, Different, Identical). You can also group objects by object type and operation.

To see the differences in the structure between Target and Source, select the object in the Different group of the grid.

For synchronization, you can exclude differences that you do not want to synchronize by clearing the check box next to the corresponding object. The number of objects to be deployed is shown in the upper-right corner.

In addition, you can preview the synchronization script for the selected object to make sure that you get the desired synchronization result. To do this, select the object, right-click it, and then select View Schema Update script.

Analyzing comparison results

Using the Object Filter

The Object Filter feature allows filtering objects in the comparison document (.scomp file). You can use a wide range of default filters or create custom ones. To open the Object Filter pane, do one of the following:

  • Right-click the comparison document and select Objects Filter or press Ctrl+L
  • On the Schema Comparison toolbar, click Filter icon on the Schema Comparison toolbar Apply a custom filter to the results

In the Object Filter pane, you can import the previously created filter, modify the default filters, or remove it if needed. In the Filter Editor, you can specify filter rules and conditions.

Filtering objects in the comparison document

Synchronizing database structures

After you selected the different objects for synchronization to create a deployment script in the Analyzing comparison results step, click Synchronize objects to the target databaseSynchronize objects to the target database on the Schema Comparison page. In the Schema Synchronization Wizard that opens, you can generate a synchronization script and tune the synchronization results based on your needs. If you want to generate the synchronization script with the default settings, click Synchronize.

To customize synchronization, do the following:

  1. On the Output tab, select the output options to manage the script and then click Next.
  2. On the Options tab, set the synchronization options and then click Next. For more information about the options, see Setting synchronization options.
  3. On the Additional Scripts tab, insert the script you want to execute after or before schema synchronization and then click Next. This step is optional.
  4. On the Summary tab, view the synchronization warnings and actions that will be taken and then click Synchronize.

Synchronizing database structures

Managing SQL scripts

As you can see, the accurate and easy-to-read synchronization script was successfully generated. You can review and execute the script, or run it against the target database.

Managing SQL script

Brief video guide to dbForge Schema Compare

Finally, you can get acquainted with everything you need to know about comparing and syncing two SQL Server databases using dbForge Schema Compare in this brief yet informative video guide.

Conclusion

dbForge Schema Compare for SQL Server is an advanced tool that helps compare two SQL Server databases in the most efficient way. As a data source, you can compare the database, backup, scripts folder, source control, and snapshot and set different options and filtering conditions to customize the default behavior of schema comparison and synchronization. Refer to our documentation center to learn more about how to compare table schemas between two databases using Schema Compare in detail.