Compare data in multiple databases from the command line

dbForge Data Compare for SQL Server allows automating and scheduling routine daily comparison and synchronization tasks using the command-line interface.

Could dbForge Data Compare for SQL Server synchronize a database schema from a single source database to multiple target databases on different servers?

The tool effectively compares data across multiple SQL Server databases. It also provides seamless automation to synchronize multiple databases across different SQL Server instances directly from the command line.

Comparing databases using the command line includes the following steps:

1. Create a text file with source and target databases and servers

2. Create a .bat file

3. Compare source and target databases from the command line

In our example, we’ll compare databases located on different servers.

Create a text file with source and target databases and servers

1. Open any third-party text editor and enter the names of the source servers and databases, separated by commas.

dbfsqlsrv\SQL2016, AdventureWorks2016, TestUser1, Password1
dbfsqlsrv\SQL2016, BicycleStoreDev, TestUser2, Password2

where:

dbfsqlsrv\SQL2016 is the source server name to which the databases are connected.
AdventureWorks2016 and BicycleStoreDev are the names of the source databases to be compared. Note that in the mentioned example, they are located on the dbfsqlsrv\SQL2016 server, but your databases can be located on different servers.
TestUser1, TestUser2, Password1, and Password2 are the login credentials that users connect to the SQL Server.

Note

You can add as many servers and databases as you need.

server_name1, database_name1, TestUser1, Password1
server_name2, database_name2, TestUser2, Password2
server_name3, database_name3, TestUser3, Password3
server_nameN, database_nameN, TestUserN, PasswordN

2. Save the file, for example, as source_servers_databases.txt.

3. In the third-party text editor, open a new file and enter the names of the target servers and databases, separated by commas.

dbfsqlsrv\SQL2019, AdventureWorks2019, TestUser3, Password3
dbfsqlsrv\SQL2019, BicycleStoreProd, TestUser4, Password4

where:

dbfsqlsrv\SQL2019 is the target server name to which the databases are connected.
AdventureWorks2019 and BicycleStoreProd are the names of the target databases to be compared. Note that in the mentioned example, they are located on the dbfsqlsrv\SQL2019 server, but your databases can be located on different servers.
TestUser3, TestUser4, Password3, and Password4 are the login credentials that users connect to the SQL Server.

4. Save the file, for example, as target_servers_databases.txt.

Create a .bat file

In the third-party text editor, enter the script for comparing data from the source and target databases and save the file.

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

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

FOR /F "eol=; tokens=1,2,3,4* delims=, " %%i in (target_servers_databases.txt) do (

%Compare% /datacompare /source connection:"Data Source=%%e;Encrypt=False;Enlist=False;Initial Catalog=%%f;Integrated Security=False;User ID=%%g;Password=%%h;Pooling=False;Transaction Scope Local=True" /target connection:"Data Source=%%i;Encrypt=False;Enlist=False;Initial Catalog=%%j;Integrated Security=False;User ID=%%k;Password=%%l;Pooling=False;Transaction Scope Local=True" /log:"D\data_compare_sql_log.log" 

) 

)
pause

where

source_servers_databases.txt is the file name with the list of source servers and databases.
target_servers_databases.txt is the file name with the list of target servers and databases.
D:\data_compare_sql_log.log is a path to the file to store the output result.

Note

Set Compare is a default installation path for dbForge Data Compare for SQL Server. If you have changed the path, you must specify the correct path to the datacompare.com file.

To compare one database with multiple databases, proceed with these steps:

1. Add the database you want to compare to source_servers_databases.txt and save the changes.

2. Add required databases to the target_servers_databases.txt file. Ensure to save the changes.

In case you need to compare one database with multiple databases and another database with multiple databases, follow these steps:

1. Create copies of the source_servers_databases.txt and target_servers_databases.txt, and .bat files, but with different names. For example, source_servers_databases01.txt, target_servers_databases01.txt, run01.bat.

2. Add the necessary databases to the source and target files (source_servers_databases01.txt, target_servers_databases01.txt).

3. Create a new .bat file to run other .bat files in sequence. For example:

call run01.bat 
call run02.bat 

Note

Ensure no pause command exists in the run01.bat and run02.bat files; otherwise, the next run operation will not start.

Compare source and target databases from the command line

1. Run the Command Prompt.

Note

To run the Command Line in Windows 10:

From the Desktop Mode

Move the mouse cursor to the lower left corner until the desktop shortcuts menu appears.

Right-click to see the shortcut menu and select Run.

From the Charms Bar

Move your mouse to the upper right corner until the Charms Bar appears.

Select Apps from the list and type run in the search box.

Click Run from the search results.

Use Windows + R Shortcut Keys

In desktop mode, press the Windows + R key combination to show the Run command line.

In the window that opens, type cmd and press Enter.