How to Compare Multiple Databases from the Command Line

June 3rd, 2021

In this article, we share a step-by-step guide on how to compare the schema and data of multiple SQL Server databases from the command line.

Both dbForge Schema Compare for SQL Server and dbForge Data Compare for SQL Server support the command-line interface, which gives the user a rich control over the tools and allows automating and scheduling regular database comparison and synchronization tasks.

As a DBA or SQL Server developer, you may face situations when you need to compare schema and/or data in more than two databases. Can you do it in one go and save a lot of time and effort? Let’s check.

Step 1. Create a text file with the list of source and target databases and servers

1.1 Launch any third-party text editor, for example, Notepad.

1.2 Enter the names of the source servers and databases, separated by commas. Here you can write as many servers and databases as you need. Below is the template for such a list:

Source_server_name1, Source_database_name1

Source_server_name2, Source_database_name2

Source_server_name3, Source_database_name3

...

Source_server_nameN, Source_database_nameN

In this worked example, we are going to use as Source the following databases on the following servers:

DBFSQLSRV\SQL2016, AdventureWorks2019_Dev
DBFSQLSRV\SQL2016, BicycleStoreDev
DBFSQLSRV\SQL2016, BicycleStoreDev1

1.3 Save the file. We will save the file with the name Source_Databases.txt.

1.4 Repeat the previous step for the target servers and databases by entering their names separated by commas according to the template:

Target_server_name1, Target_database_name1

Target_server_name2, Target_database_name2

Target_server_name3, Target_database_name3

...

Target_server_nameN, Target_database_nameN

In this worked example, we are going to use as Source the following databases on the following servers:

DBFSQLSRV\SQL2019, AdventureWorks2019_Test
DBFSQLSRV\SQL2019, BicycleStoreDev
DBFSQLSRV\SQL2019, BicycleStoreDev1

1.5 Save the file. We will save the file with the name Target_Databases.txt.

Step 2. Create a .bat file

2.1 Launch any third-party text editor, for example, Notepad.

2.2 Enter the script for comparing databases like in the examples below. Don’t forget to adjust the script to suit your needs.

The script for comparing schemas of multiple databases from the command line:

Set Compare="C:\Program Files\Devart\Compare Bundle for SQL Server Professional\dbForge Data Compare for SQL Server\schemacompare.com"

FOR /F "eol=; tokens=1,2* delims=, " %%e in (Source_Databases.txt) do (

FOR /F "eol=; tokens=1,2* delims=, " %%g in (Target_Databases.txt) do (

%compare% /schemacompare /source connection:"Data Source=%%e;Encrypt=False;Enlist=False;Initial Catalog=%%f;Integrated Security=False;User ID=sa;Pooling=False;Transaction Scope Local=True" /target connection:"Data Source=%%g;Encrypt=False;Enlist=False;Initial Catalog=%%h;Integrated Security=False;User ID=sa;Pooling=False;Transaction Scope Local=True" /log:"C:\log_files\dc_log.log"

)

)

pause

The script for comparing data of multiple databases from the command line:

Set Compare="C:\Program Files\Devart\Compare Bundle for SQL Server Professional\dbForge Data Compare for SQL Server\datacompare.com"

FOR /F "eol=; tokens=1,2* delims=, " %%e in (Source_Databases.txt) do (

FOR /F "eol=; tokens=1,2* delims=, " %%g in (Target_Databases.txt) do (

%compare% /datacompare /source connection:"Data Source=%%e;Encrypt=False;Enlist=False;Initial Catalog=%%f;Integrated Security=False;User ID=sa;Pooling=False;Transaction Scope Local=True" /target connection:"Data Source=%%g;Encrypt=False;Enlist=False;Initial Catalog=%%h;Integrated Security=False;User ID=sa;Pooling=False;Transaction Scope Local=True" /log:"D:\dc_log.log"

)

)

pause

Where:
Source_Databases.txt is the name of the file listing source servers and databases.
Target_Databases.txt is the name of the file listing source servers and databases.
D:\dc_log.log is a path to the file where the output result will be stored.

Note: Set Compare is a default installation path for dbForge Data Compare and dbForge Schema Compare. However, if you have changed it, you will need to specify the correct path to the required tool’s .com file as well.

2.3 Save the script.

Step 3. Compare source and target databases via the command line

Now, all you need to do is execute the .bat file via the command line.

First, let us run the .bat file for comparing schemas in our set of databases.

Schema Compare in multiple databases result

Now, let us run the .bat file for comparing data in our databases.

Data Compare multiple databases result

You will instantly get the summary comparison results: whether the source and target databases are identical or not, how many different or conflicting records there are, etc. The output file will be generated after the successful completion of the process.

Conclusion

dbForge Schema Compare and dbForge Data Compare tools include the command-line interface (CLI) for performing schema comparison and deployments of SQL Server databases from the command line. This article provides worked examples of CLI scripts for comparing SQL Server schemas and data across multiple databases.

Download Compare Bundle for SQL Server
Leave a Comment