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.
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
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
4. Save the file, for example, as target_servers_databases.txt.
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
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.
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.