Compare and Synchronize Schemas with dbForge Studio for SQL Server

Comparing database schemas is the first step in the database comparison process, and it can be accomplished with the help of feature-rich dbForge Studio for SQL Server. You can benefit from the tool's functionality when you need to:

Start Comparison Process

With the Schema Comparison wizard, you can compare database schemas, backups, database snapshots, script folders, and database schemas stored in source control.

In the wizard, you choose source and target, customize the process by setting up the required options. For further process automation, your settings can be saved to a file that will be used in the command-line interface.

To prevent any confusion between source and target instances, you may use the Categories feature.

Analyze Schema Comparison Results

When the comparison process is complete, the results stating objects, object status, and operation are displayed in the grid. Click the necessary object to get a detailed view of it in the text comparer below.

To save results for further analysis or distribute them, you can generate a comparison report in HTML, Excel, or XML format.

Work with Schema Comparison Document

The Schema Comparison document is a file with a .scomp extension that contains schema comparison results. You can manage the results in different ways and save the document for future use. For instance, you can later load command-line settings using the comparison .scomp project file. The Comparison Document allows you to:

• Apply filters, sort, and group objects in the result.
• Exclude and include objects to continue working with them.
• View DDL differences for each object pair.
• Preview the schema update script for each object.

Apply Object Filter

The Object Filter feature enables you to:

• Filter objects right in the Comparison Document.
• Modify the existing filters and create new custom filters.
• Apply multiple filters simultaneously.
• Save customized filters for further comparisons across multiple projects.
• Use the filter from the command line.

Generate Comparison Report

A handy wizard allows you to create and export the results of the comparison in the form of a report. The comparison report contains essential information, including Source and Target details as well as the summary of the schema comparison in a convenient form of the list. The report is easily customizable, and you can exclude and include objects, add warnings, and append timestamps to the report file. You can choose from three file formats to create a report that suits your needs best:

• HTML - a Hypertext Markup Language file format is suitable for publishing the report on the web. HTML extension also allows presenting script differences in a report as:
  • Removed-added lines
  • Crossed out words
• Excel - one of the most popular spreadsheet file formats.
• XML - an extensible markup language file format used to share the data on the World Wide Web.

Compare Source Control Versions

The schema comparison feature within dbForge Studio for SQL Server allows comparing and synchronizing source control versions of your database. The tool enables you to define a source control version either as a target or as a source as well as to create a new SQL source control version.

All you need to do is to specify the necessary database version and select from a list of source control systems, such as Team Foundation Server (TFS), Subversion (SVN), SourceGear Vault, Git, Perforce, Mercurial, and others the one you are working with.

Synchronize SQL Server Schemas

As soon as you have selected the objects you need to synchronize, just call the Synchronization wizard.

In the wizard, you can customize the process by setting output and synchronization options. After synchronization, you get a comprehensive script to apply updates and changes to your databases.

Create Deployment Script

Using the Schema Synchronization Wizard, you can generate an update script to synchronize your database schemas. You will be able to:

• View, edit and save the deployment script.
• Select a database backup, snapshot, or script folder either as source or target.
• Migrate any schema differences (individual, select, or all).
• Include dependent objects into the synchronization.
• Customize the synchronization options to your needs and save them as your defaults for future use.
• Resolve any synchronization issues automatically.
• View the list of warnings before the synchronization.

Set up Continuous Integration

The Schema Comparison feature is an integral part of the Continuous Integration (CI) process as it greatly facilitates database development and deployment. Depending on the chosen database development model, you can apply it in either a state-based or a migration-based approach. Use the schema comparison feature to:

• Deploy a database from a script folder on a SQL Server instance.
• Alter a database schema and manage the differences.
• Update the script folder to introduce the necessary changes to the schema.
• Perform error-free schema synchronization using generated SQL scripts
• Automate database synchronizations via command line.

Automate Comparison and Synchronization

The command-line interface allows scheduling comparison and synchronization without actually participating in the process. The options saved from the wizard can be used for customization. Make use of the following automation capabilities:

• Save comparison project settings for future use.
• Apply the tool's integrated script generation feature to obtain a PowerShell automation script as a .bat file.
• Schedule the script execution via the Windows Scheduler to automate the schema comparison process.