Compare schemas in multiple databases from the command line

Last modified: July 4, 2024

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

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

The tool effectively compares schemas 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 schemas 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 schemas from the command line

In our example, we’ll compare schemas 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 with which 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 schemas on the source and target servers and save the file.

Set Compare="C:\Program Files\Devart\dbForge SQL Tools Professional\dbForge Schema Compare for SQL Server\schemacompare.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% /schemacompare /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\schema_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:\schema_compare_sql_log.log is a path to the file to store the output result.

Note

The Set Compare command specifies a default installation path for dbForge Schema Compare for SQL Server as a part of dbForge SQL Tools. If you have changed the path, you must specify the correct path to the schemacomare.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 schemas 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.

2. Execute the .bat file. After that, the log file containing the output result will automatically be generated.

The screenshot shows the comparison of the BicycleStoreDev and BicycleStoreProd databases.

The screenshot shows the comparison of the AdventureWorks2016 and AdventureWorks2019 databases.

See also: Schedule database synchronization.

Helpful materials

Data and Schema Comparison Log Delivery to Email

Study the cases to configure automatic email delivery of log files, both when the comparison goes smoothly and when it fails.

How to Automatically Synchronize Schema Changes in Two SQL Server Databases on a Schedule

Explore how to automate and schedule the synchronization process of two SQL Server databases.

Master the Art of Database Comparisons with PowerShell and dbForge Studio for SQL Server

Discover the process of automating database comparisons using PowerShell and dbForge Studio for SQL Server. Additionally, if you substitute the path in the $diffToolLocation variable with C:\Program Files\Devart\dbForge SQL Tools Professional\dbForge Schema Compare for SQL Server\schemacompare.com, you can streamline the automation of the comparison process using dbForge Schema Compare for SQL Server.