Compare schemas in multiple databases from the command line
dbForge Schema Compare for SQL Server allows automating and scheduling routine daily comparison and synchronization tasks using the command-line interface.
Comparing schemas using the command line includes the following steps:
1. Create a text file with source and target databases and servers
3. Compare source and target schemas from the command line
In our example, we are going to 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 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 with which users connect to the SQL Server.
Note
You can add as many servers and databases as you need.
server_name1, database_name1 server_name2, database_name2 server_name3, database_name3 ... server_nameN, database_nameN
2. Save the file, for example, as source_servers_databases.txt.
3. In the thrid-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 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 thrid-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* delims=, " %%e in (source_servers_databases.txt) do (
FOR /F "eol=; tokens=1,2* delims=, " %%g 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=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:\schema_compare_sql_log.log"
)
)
pause
where
- source_servers_databases.txt is the name of the file with the list of source servers and databases.
- target_servers_databases.txt is the name of the file 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 will need to specify the correct path to the schemacomare.com file.
Compare source and target schemas from the command line
1. Run the Command Prompt. For this, click Start, and type cmd in the Run line. Press ENTER.
2. Execute the .bat file. After that, the log file will automatically be generated containing the output result.
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.