Compare schemas in multiple databases from the command line

You can use the command-line interface to:

  • Automate and schedule routine database comparison and synchronization tasks.
  • Compare and synchronize schemas across multiple SQL Server databases.

Workflow

Schema comparison across multiple databases includes:

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

2. Creating a .bat file.

3. Comparing source and target schemas from the command line.

Create a text file with the source and target databases and servers

1. Open any third-party text editor.

2. Enter the connection details for the source servers and databases. Separate each value with a comma.

server_name1, database_name1, TestUser1, Password1
server_name2, database_name2, TestUser2, Password2

where:

  • server_name1 and server_name2: The names of the source servers where the source databases reside.
  • database_name1 and database_name2: The names of the source databases you want to compare.
  • TestUser1, TestUser2, Password1, and Password2: The login credentials for those servers.

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

4. Repeat the same process for the target servers and databases, and save the file as target_servers_databases.txt.

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

Create a .bat file

1. Open a text editor.

2. Create a new file and enter the following script:

Set Compare="C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.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:"schema_compare_sql_log.log" 

) 

)

where:

  • Set Compare: The default installation path for dbForge Studio for SQL Server. If you installed dbForge Studio in a different location, update the path to the dbforgesql.com file.
  • source_servers_databases.txt is the text file that contains the source servers and databases.
  • target_servers_databases.txt is the text file that contains the target servers and databases.
  • schema_compare_sql_log.log is the path to the log file that will store the results.

3. Save the file with a .bat extension, for example, run_compare.bat.

Compare one database to multiple target databases (single run)

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

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

Compare one or more databases in separate runs

1. Create copies of the source_servers_databases.txt, target_servers_databases.txt and .bat files with different names. For example:

  • source_servers_databases01.txt
  • target_servers_databases01.txt
  • run01.bat

2. Add the required databases for the first comparison to source_servers_databases01.txt and target_servers_databases01.txt, then save them.

3. For each additional comparison, create new .txt and .bat files. For example:

  • For the second comparison:

  • source_servers_databases02.txt
  • target_servers_databases02.txt

  • run02.bat

  • For the third comparison:

  • source_servers_databases03.txt
  • target_servers_databases03.txt

  • run03.bat

  • Continue this pattern for subsequent comparisons.

3. Create a new .bat file, for example, run_all.bat, that will execute the comparison scripts in sequence:

call run01.bat 
call run02.bat

Each call command runs the respective .bat file (such as run01.bat and run02.bat), performing the comparison defined in those files.

Note

Ensure that the run01.bat and run02.bat files do not contain any pause commands or subsequent run operations will not start.

Run the comparison from the command line

1. Open Command Prompt (CMD).

2. Execute the .bat file.

A log file containing the output results will be generated automatically.

Schema Comparison of AdventureWorks2022 and AdventureWorks2022Dev databases

Compare source and target schemas from the command line

Schema Comparison of BikeStores and BikeStoresDev databases

Compare source and target schemas from the command line