Step-by-Step Guide to dbForge Schema Compare for SQL Server

Step-by-Step Guide to dbForge Schema Compare for SQL ServerdbForge Schema Compare for SQL Server is a tool designed to help database administrators and developers compare and synchronize database schemas quickly and accurately. This guide walks you through installing the tool, preparing your environment, performing comparisons, reviewing differences, generating synchronization scripts, and best practices to use it confidently in development and production workflows.

What you’ll need

• dbForge Schema Compare for SQL Server (standalone tool or as part of dbForge Studio for SQL Server).
• A Windows machine with network access to your SQL Server instances.
• Credentials with appropriate permissions to read schema metadata on both source and target databases.
• Optional: a version control system and a staging environment for safer deployments.

1. Installation and initial setup

1. Download dbForge Schema Compare for SQL Server from the vendor site or install it as part of dbForge Studio.
2. Run the installer and follow prompts. Choose the edition (trial, standard, or enterprise) according to your license.
3. Launch the application. If using dbForge Studio, open the Schema Compare tool from the Tools or Database menu.
4. Configure connection profiles for the SQL Server instances you will use:
   • Server name (or IP)
   • Authentication method (Windows or SQL Server)
   • Database selection
   • Save credentials securely if permitted by your security policy

2. Preparing databases for comparison

• Ensure both source and target databases are accessible and not undergoing heavy schema changes during comparison.
• Back up the target database before applying any synchronization to prevent accidental data loss.
• If available, use a staging environment that mirrors production for testing synchronization scripts first.
• Optionally, create snapshots or schema dumps as a baseline for repeatable comparisons.

3. Creating a new comparison

1. Open Schema Compare and click “New” (or File → New → Schema Comparison).
2. Set the source and target:
   • Source: the database or a schema snapshot you want to compare from.
   • Target: the database you want to synchronize to.
3. Choose options:
   • Compare all objects or select specific object types (tables, views, stored procedures, functions, triggers, users, roles).
   • Set advanced options such as ignoring white space in definitions, case sensitivity, or permission differences.
4. Start the comparison. dbForge will analyze schema metadata and show results grouped by object type and status (identical, different, only in source, only in target).

4. Understanding comparison results

• Results pane lists objects with icons or color coding indicating:
  • Equal — no differences.
  • Different — object exists in both but definitions differ.
  • Only in Source — object exists only in the source.
  • Only in Target — object exists only in the target.
• Selecting an object shows a detailed side-by-side diff of object DDL with syntax highlighting, making it easy to spot specific changes (column additions, datatype changes, constraint modifications, etc.).
• Use filters to focus on certain object types, statuses, or specific name patterns.

5. Resolving differences and generating synchronization scripts

1. Pick which changes to apply:
   • Use the checkboxes next to objects to include or exclude them from synchronization.
   • For objects with complex differences, open the object-level diff to choose specific parts of the DDL to apply (e.g., modify a column but not constraints).
2. Configure synchronization options:
   • Execution mode: generate a SQL script, execute directly against the target, or create a rollback script.
   • Transaction behavior: wrap changes in a single transaction or commit per object.
   • Scripting options: include DROP statements, handle dependencies automatically, or create backups.
3. Generate the synchronization script:
   • Review the automatically generated script carefully.
   • Modify manually if needed to account for environment-specific considerations (data migrations, manual steps for incompatible changes).
4. (Recommended) Test the script in a staging environment first.
5. Apply the script to the target database once validated.

6. Advanced features and tips

• Project support: Save comparison settings as a project to reuse configurations for regular comparisons between the same databases.
• Command-line automation: Use dbForge’s command-line interface to integrate schema comparisons into CI/CD pipelines for automated checks and deployments.
• Schema snapshots: Compare a live database against a saved snapshot or a script folder to verify drift or prepare for migrations.
• Ignore options: Use ignore lists for object names or whitespace and formatting differences to reduce noise in results.
• Dependency resolution: Enable options to reorder operations or include dependent objects automatically to avoid execution errors during synchronization.
• Data preservation: For changes that affect columns with data (e.g., dropping or changing column types), add manual migration steps in the script to preserve and transform data safely.

7. Common scenarios and examples

• Schema drift detection: Schedule comparisons between production and a reference schema to detect unintended changes.
• Deployment from development to production: Compare the development DB with production, generate a script, test in staging, and apply to production during a maintenance window.
• Migrations between versions: Use Schema Compare to identify breaking changes and create a migration plan that includes data transformation steps.
• Merging branches: Compare database schemas from different development branches and selectively merge schema changes.

8. Troubleshooting common issues

• Permission errors: Ensure the account used has VIEW DEFINITION and appropriate DDL modification rights on the involved databases.
• Long comparison times: Narrow comparisons to necessary object types, use snapshots, or run during low-traffic windows.
• Dependency failures on sync: Enable automatic dependency resolution or manually order changes to fulfill dependencies (create tables before foreign keys).
• Unexpected differences due to formatting: Use ignore whitespace or ignore comments options to focus on semantic differences.

9. Best practices

• Always back up target databases before applying changes.
• Use staging environments for testing synchronization scripts.
• Automate comparisons in CI/CD to catch schema drift early.
• Keep schema comparisons and migration scripts under version control.
• Review generated scripts manually to handle complex data-preserving changes.
• Communicate planned schema changes with application and operations teams to schedule downtime if needed.

10. Conclusion

dbForge Schema Compare for SQL Server accelerates schema comparison and synchronization while reducing human error. By following this step-by-step guide — installing correctly, preparing databases, carefully reviewing differences, testing generated scripts, and using automation — you can safely manage schema changes across development, staging, and production environments.