Automate schema comparison and synchronization from the command line
The guide describes the following topics:
- Prepare a file with command-line arguments
- Generate a file with command-line arguments
- Generate a file name dynamically
You can automate schema comparison and synchronization using the command line.
Note
To run the Command Line in Windows 10:
From the Desktop Mode
- Move the mouse cursor to the exact lower left corner until the desktop shortcuts menu appears.
- Right-click to see the shortcut menu and than click 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 keys, to show the Run command line.
- In the window that opens, type cmd and press Enter.
1. Type a path to the schemacompare.com file, located in dbForge Schema Compare for SQL Server installation folder on your PC, and press SPACEBAR.
2. Type all the arguments (actions you want to run) in the Command Prompt. You can do this in three ways:
- Type all the arguments directly in the Command Prompt, for example:
/schemacompare /source connection:"Data Source="source_name";Integrated Security=False;User ID=yourusername;
Password=yourpassword;database=database_name" /target connection:"Data Source="target_name";
Integrated Security=False;User ID=yourusername;Password=yourpassword;database=database_name"
- Specify a path to a previously saved comparison document to use the connection settings along with the comparison settings specified in the document.
/schemacompare /compfile:"D:\file_name.scomp"
- Type the path to a previously generated file with required command-line arguments (see below how to prepare the file), for example:
/argfile:"D:\file_name.txt"
Type additional parameters that are not specified in the file into the Command Prompt, for example:
"C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Schema Compare for SQL Server\schemacompare.com" /argfile:file_name.txt /schemacompare /sync
Note
The path to the executable file will differ depending on which tools suite you have installed Schema Compare with - Compare Bundle or SQL Tools. Ensure you have specified a correct path.
If you have installed Schema Compare as part of SQL Tools, change the path to:
C:\Program Files\Devart\dbForge SQL Tools Professional\dbForge Schema Compare for SQL Server\schemacompare.com
Tip
To see a quick help on the arguments available in the command line, type the following:
schemacompare.com /?schemacompare.com /schemacompare /?
Press ENTER to run the process.
Prepare a file with command-line arguments
1. Define what action you want to run, for example:
/schemacompare
2. Define the Source and Target connection strings:
/schemacompare /source connection:"Data Source="DBMSSQL\MSSQL2012";Integrated Security=False;User ID=yourusername;
database=AdventureWorks" /target connection:"Data Source="DBMSSQL\MSSQL2012";Integrated Security=False;
User ID=yourusername;database=AdventureWorks"
You can also use the schema comparison document that already contains the Source and Target connection strings.
/schemacompare /compfile:"C:\MyComparison.scomp"
3. To apply a custom object filter, saved as an .scflt file, specify the file name and location. For example:
/filter:"C:\Custom.scflt"
To learn more about using object filters in Schema Compare, refer to Use object filter.
4. To generate and save a log file, specify the file name and location. For example:
/log:"C:\Log.log"
5. To generate and save a comparison report file, specify the file name and location. For example:
/report:"C:\Report.html"
6. To synchronize the schemas and update the Target schema, type the following:
/sync
7. To generate and save a synchronization script, after the /sync argument, specify the file location and name. For example:
/sync:"C:\sync_script_name.sql"
Note
For more information on how to create and save the schema synchronization script, refer to Review and save the deployment script.
Generate a file with command-line arguments
To generate a file with command-line arguments, do the following:
1. Open the Schema Comparison wizard using one of the following ways:
- On the standard toolbar, click New Schema Comparison.
- On the Start page, click New Schema Comparison.
- On the File menu, click New > New Schema Comparison.
- On the standard toolbar, click Edit Comparison if you want to generate a file for the already executed schema comparison and want to change comparison properties.
2. On the Source and Target page of the wizard, select the source and target data sources you want to compare.
3. Optional: You can go through other wizard pages to configure the data comparison.
4. On the Options page, click Save settings to a command line arguments file in the upper right corner. In the Save As window that opens, specify the file name and location to save the file and then click Save.
The selected Source and Target connection strings along with comparison options will be saved as a .txt file with command-line arguments. You can edit the values of comparison options in the file as described in Options used in the command line.
Note
The generated file does not contain password information for security purposes. You should edit the file and enter the passwords manually.
For more information about the available additional arguments you can use with the /schemacompare arguments, see Switches used in the command line.
The command line string with /argfile parameter will be written as follows:
C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Schema Compare for SQL Server>schemacompare.com /argfile:file_name.txt
Generate a file name dynamically
To begin with, you need to create a batch file that will generate a file name dynamically during the execution. The timestamp for the current day will be added to the file name automatically.
To do this, perform the steps:
1. In any third-party text editor, for example, Notepad or Visual Studio Code, open a new blank document and enter the following script:
set TimeStamp=%date:~6,4%-%date:~3,2%-%date:~0,2%_%time:~0,2%-%time:~3,2%-%time:~6,2%
"C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Schema Compare for SQL Server\schemacompare.com" /schemacompare /source connection:"Data Source=DBFSQLSRV\SQL2022;Initial Catalog=AdventureWorks2022;Integrated Security=False;User ID=yourusername" /target connection:"Data Source=demo-mssql\SQLEXPRESS02;Initial Catalog=AdventureWorks2022Test;Integrated Security=False;User ID=yourusername" /log:"D:\Log_File_%TimeStamp%.log" /sync:"Update_Script_%TimeStamp%.sql"
The following data should be replaced with your actual parameters:
| Parameter | Description |
|---|---|
| D:\Log_File_%TimeStamp%.log | Path to the output file that stores log data |
| Update_Script_%TimeStamp%.sql | Name of the file that stores the generated synchronization script. The file will be saved in the same directory as the batch file |
2. Save the file with the .bat extension.
After the batch file has been created, navigate to the file location and double-click the file to execute it. The result will be written to the log file with a timestamp added to its name for the current day. It might be as follows:
See also:
- How to automatically track database schema changes with PowerShell scripts.
- How to set up email delivery for schema comparison logs
Want to Find out More?
Overview
All features
Request a demo
