The dbForge Schema compare tool is a SQL tool that is used to compare the schema of two databases that exists on one or multiple instances of the SQL Server.
Recently, one of my company's clients was facing an issue in their application that accessed the archived database. After troubleshooting the issue, we came to know that the archived database was out of sync with the production database. The schema was out of sync because the execution of a few DDL scripts failed, and the schema remained unchanged. We fixed the issue by writing a stored procedure that compares the schema between production and archived databases and provides the list of missing objects.
Later, we learned about the dbForge Schema Compare tool that can perform the comparison of two SQL databases very easily. It provides the following benefits
- Compare the source and destination database and objects.
- Generate the scripts that can be used to create an object missing in the source/target database.
- Generate the report which provides the details of the missing objects.
We can compare the following types of objects in the dbForge Schema Compare tool.
- SQL Server database: This option is used to compare the database objects of the source and destination database.
- Backup of SQL Server database: This option is used to compare the backup of the SQL Server database.
- Script Folder: This option compares the source and destination folders containing the script to create database objects.
- Source control repository: This option compares two source control repositories that contain the T-SQL database scripts.
In this article, we will learn to compare the objects of two databases. To demonstrate, I have installed SQL Server 2019 and restored two databases named statckoverflow2010 and Stackoverflow2010_old. The source database is stackoverflow2010, and the target database is stackoverflow2010_old. We will find the list of objects that are changed/missing in the target database (Stackoverflow2010_old).
First, click on New schema compare located below the menu bar.
The New schema comparison wizard starts. You can configure the following options:
- Source and target databases which you want to compare.
- Set advance option to customize sql compare process. Schema and table mapping.
Configure the source and target database.
First, let us configure the source and destination database. In our case, the source database is Stackoverflow2010, and the target database is stackoverflow2010_old.
We compare the databases; therefore, select Database from the Type drop-down box. In the Connection drop-down box, you can select the existing connection or create a SQL Server connection on which the source and destination databases are created. We are creating a new connection; therefore, select Manage.
Another dialog box named opens. Here you can see the various parameters and configuration options to create a SQL Server database connection. Click on New to create a New connection.
In our case, the configuration parameters are specified as follows.
- Server Name: Specify the server's hostname on which the SQL Server is installed. In our case, the hostname is Nisarg-PC.
- Authentication: select the authentication type. You can use windows authentication or SQL Server authentication. In our demo, we are using Windows authentication.
- Database: select the source database. In this demo, the source database is stackoverflow2010.
- Connection name: Enter the desired connection name.
Click Connect.
Back to the connection manager dialog box, you can see the new connection has been created. Following is the screenshot of the Connection Manager dialog box.
Now, similarly, configure the Target connection. In this demo, the target database is Stackoverflow2010_old on the same SQL Server instance. Therefore, we will change the database name in the Target SQL Server connection Manager. The parameters in the dialog box named Database Connection Properties are following:
- Server Name: Specify the hostname of the server on which the SQL Server is installed. In our case, the hostname is Nisarg-PC.
- Authentication: select the authentication type. You can use windows authentication or SQL Server authentication. In our demo, we are using Windows authentication.
- Database: select the source database. In this demo, the target database is stackoverflow2010_old.
- Connection name: Enter the desired connection name.
Click Connect.
In the Connection Manager dialog box, you can see the new connection has been created.
The source and target screen look like the following image. Click Next to configure the options.
Schema Mapping
You can set the correspondence between the source and target schema owners in the schema mapping screen. Suppose you are comparing the two databases. The source database has two schemas that the target database doesn't have. The stackoverflow2010 database has two schemas in this demo, but the stackoverflow2010_old database does not have any schema. The wizard automatically sets in the target schema grid-view on the schema mapping screen.
Table Mapping
You can choose the list of the database objects you want to compare on the table mapping screen. Also, you can map columns to compare the data type. We will learn more about it in our next article. If you want to exclude any objects from the mapping list, right-click on the object name and click on Unmap.
In our demo, we are comparing all objects.
Begin schema compare process
Once mappings are completed, click on Compare to begin the database comparison process. If the Database is large and contains many objects, the process takes a while to complete.
Once the comparison completes, you can see the object difference shown in the following image.
The comparison project provides the following details
Objects that exist in the only source database.
Objects that exist in both databases but have different values of parameters.
Objects that exist in the target database.
The comparison project shows the T-SQL queries which can be used to synchronize/change the source and destination database.
In our demo, the details provided by the schema comparison is following:
Only in the Source database
The Stackoverflow2010 database contains two schemas named UserPosts and Users, which do not exist in the Stackoverflow2010_old database. The comparison project also generates the CREATE SCHEMA statement. See the following image:
Different in source and destination database
There are two differences found in the source and destination databases.
- The database recovery model.
- Stackoverflow2010: SIMPLE recovery model.
- Stackoverflow2010_old: FULL recovery model.
- An additional column named description is in the comment table.
See the following images for reference:
Image 1: Database recovery model
Image 2: Column differences in the comment table
Only in the Target database
The Stackoverflow2010_old database contains the tblArchivedPosts table that does not exist in the stackoverflow2010 Database. The comparison project also generates the CREATE TABLE statement that might be used to create a table in the source database.
As you can see, the schema compare tool has provided detailed information on the differences between the source and target database.
Summary
The above article explains the process of comparing the SQL databases using dbForge schema compare. In my upcoming articles, we will review the process to compare the backups of the SQL databases.
Top comments (0)