Update: SolarWinds DBA xPress is now a FREE tool. In versions 2021.8 and later it is a standalone product and no longer requires a license.
Download: See the DBA xPress product page to learn more.
Overview
TheDBA xPress command line feature executes schema and data comparisons that are available in the application through a command line interface. Saved scenarios may be called to save time by using defined connection string information as well as desired options in your commands. Arguments let you control whether a script is only generated, directly executed, or both to perform a sync operation. See the table below for available arguments and options.
There are two execution methods available: schemacompare and datacompare.
- Schemacompare loads a schema comparison scenario and executes the actions based on the arguments.
- Datacompare loads a data comparison scenario and executes the actions based on the arguments.
Executable
The application file is located in the SolarWinds\SolarWindsDBA xPress directory. For example, in the 2021.8 version ofDBA xPress, the full path is:
C:\Program Files (x86)\SolarWinds\SolarWindsDBA xPress
The command you execute will start with
dbaxpress.commandline schemacompareor
dbaxpress.commandline datacompare
Available Arguments
| Argument | Description |
|---|---|
| -f or --filepath | Path to the scenario file. |
| -s or --sync | Defines the type of operation the sync will perform.
Note: Script and hybrid operations require the --scriptoutput option. Warning: The direct and hybrid options execute update scripts without your review. |
| -o or --scriptoutput | Path to the generated script. |
| --sourceconnectionstring | Defines the connection string of the source side of the comparison or overrides the source connection string defined in the comparison scenario. |
| --targetconnectionstring | Defines the connection string of the target side of the comparison or overrides the target connection string defined in the comparison scenario. |
| --reportoutput | Path used to the save the generated report. Note: There is no report generation for datacompare. |
| --reportformat | Indicates the format in which the report is generated. Available formats:
Note: There is no report generation for datacompare. |
| --skipwarnings | Indicates to the program to not prompt you to ignore the warnings generated by the data comparison. Note: This argument is not available for schemacompare. |
Example of the command line syntax with arguments:
dbaxpress.commandline schemacompare -s script --sourceconnectionstring "<DataSourceString>" --targetconnectionstring "<DataSourceString>" -o <ScriptPath>.sql --reportoutput <ReportPath> --reportformat html
Examples
1. schemacompare on identical databases
This demonstrates what happens when the source and target databases have no schema differences.
dbaxpress.commandline schemacompare -s script --sourceconnectionstring "data source=localhost;initial catalog=WideWorldImporters;integrated security=SSPI;" --targetconnectionstring "data source=localhost;initial catalog=WideWorldImportersTwin;integrated security=SSPI;" -o d:\dbax\dbaxreports\melissascriptwideworldsame.sql
The output specifies "No differences found" and the script file is not generated in this scenario.
2. schemacompare with report formatted as HTML
dbaxpress.commandline schemacompare -s script --sourceconnectionstring "data source=localhost;initial catalog=AdventureWorks2017;integrated security=SSPI;" --targetconnectionstring "data source=localhost;initial catalog=AdventureWorksDW2017;integrated security=SSPI;" -o d:\dbaxreports\melissascript1.sql --reportoutput d:\dbaxreports\melissareport1 --reportformat html| In this example, the sync is set to script so that it will be generated, but not executed. The schemas are both on localhost and the connection is using SSPI authentication. The schema on the left is AdventureWorks2017 and the schema on the right is AdventureWorksDW2017. It outputs both a SQL script (melissascript.sql) and a report in a directory (melissareport1) formatted as HTML. |
|
| From the example above, the script outputs to d:\dbaxreports\melissascript1.sql and the HTML formatted report goes to d:\dbaxreports\melissareport1. |
|
| The script file (melissascript1.sql) contains the T-SQL to modify the schema. |
|
| The files for the report have been sent to the melissareport1 folder. Note: The index.html file contains the report table of contents. |
|
| Open the index.html file in a browser to view the report. |
|
| Expand the nodes under Contents to view the details. |
|
3. schemacompare with report formatted as RTF
dbaxpress.commandline schemacompare -s script --sourceconnectionstring "data source=localhost;initial catalog=AdventureWorks2017;integrated security=SSPI;" --targetconnectionstring "data source=localhost;initial catalog=AdventureWorksDW2017;integrated security=SSPI;" -o d:\dbax\dbaxreports\melissascript2.sql --reportoutput d:\dbax\dbaxreports\melissareport2.rtf --reportformat richtext| In this example, the report (melissareport2) has an RTF file format instead of the HTML. |
|
| Example of the RTF output. |
|
4. schemacompare with hybrid sync
This example shows a schemacompare with the sync set to hybrid to execute the generated script at runtime and create a report of the differences.
dbaxpress.commandline schemacompare -s hybrid --sourceconnectionstring "data source=localhost;initial catalog=WideWorldImporters;integrated security=SSPI;" --targetconnectionstring "data source=localhost;initial catalog=WideWorldImportersMod;integrated security=SSPI;" -o d:\dbax\dbaxscripts\melissawideworldmod.sql --reportoutput d:\dbax\dbaxreports\melissaworldwidemod --reportformat html
| Before excuting the command line from above, the target database (WideWorldImportersMod) has been altered to include a stored procedure (Application.AddedExtraProcedure) which does not exist on the source database (WideWorldImporters). |
|
| The command is executed.DBA xPress runs the comparison, generates the statements, outputs the script statements to the SQL file, executes the statements, and writes an HTML report. |
|
| The generated statement in the SQL file to drop the stored procedure from the target database: |  |
| The HTML-formatted report: |  |
| The database in SSMS showing the altered schema with the stored procedure no longer there: |  |
5. datacompare to create script only
dbaxpress.commandline datacompare -s script --sourceconnectionstring "data source=localhost;initial catalog=AdventureWorks2017;integrated security=SSPI;" --targetconnectionstring "data source=localhost;initial catalog=AdventureWorksDW2017;integrated security=SSPI;" -o d:\dbax\melissascript3.sql --skipwarnings| The example above creates a script for data modifications, while skipping any potential warnings. |
|
| Example of the script output. |
|
6. Example with SQL Server Errors Returned with Direct
If you receive any errors in the command line, you'll want to take a closer look to see if they are surfaced from SQL Server.
dbaxpress.commandline schemacompare -s direct --sourceconnectionstring "data source=localhost;initial catalog=AdventureWorks2017;integrated security=SSPI;" --targetconnectionstring "data source=localhost;initial catalog=AdventureWorksDW2017;integrated security=SSPI;" -o d:\dbax\dbaxscripts\melissadirectscript1.sql| This example uses the same comparison from Example 2, but uses the direct sync method to execute the scripts. In this environment, there will be SQL Server errors surfaced during the sync which are shown in the image on the right. These errors must be resolved in SQL Server beforeDBA xPress can execute these statements. |
| |
If I execute the script sync option to see the differences, I can see that TSQL was executed, but these particular statements remain due to the SQL Server errors.
|
| |
| If I attempt to run those statements in SSMS directly, I will receive the same SQL Server errors. These errors must be resolved before synchronization can complete successfully. |  |
UsingDBA xPress Scenarios
Without scenarios, commands run using the default settings. Creating and saving scenarios allows you to run commands with your customized settings and filters. See the Data Inspector and Schema Inspector articles for complete details on creating scenarios withDBA xPress.
Note: Once you have a scenario saved, you can change the --targetconnectionstring in the command line to run a comparison against a different data source using the same scenario. There is an example for this below under the heading Use a Scenario with --targetconnectionstring in Command Line.
Create a Scenario
Complete the following steps to create a scenario inDBA xPress:
| 1. OpenDBA xPress, then select the Schema Inspector (or Data Inspector) tile from the feature finder menu. Note: These steps use Schema Inspector. |  |
| 2. Enter a Name for your scenario on the Add Comparison Scenario screen, then select Next. |  |
| 3. Enter the details for the Left Hand Side of the comparison, then select Next. Note: In this example, we are using the Database tab. The SQL Server is set to localhost and the connection string authentication method is set to Integrated Security. After entering this information, the Database drop-down list is populated and AdventureWorks2017 is selected for this scenario. |  |
| 4. Enter the details for the Right Hand Side of the comparison, then select Next. Note: In this example, we are using the Database tab. The SQL Server is set to localhost and the connection string authentication method is set to Integrated Security. After entering this information, the Database drop-down list is populated and AdventureWorksDW2017 is selected for this scenario. |  |
| 5. Review the Choose Options screen, make any changes as necessary, and then select Finish. Note: Some options are checked by default. This example uses the default options. |  |
| 6. Once the scenario has been generated, be sure to save it using the Save Scenario or Save Scenario As options at the top. |  |
Note: The file type .sis is used for schema inspector scenarios and .dis is used for data inspector scenarios.
Use a Scenario in Command Line
This example uses the scenario from above within the command line:
dbaxpress.commandline schemacompare -f d:\dbax\scenarios\MelissaSchemaScenario1.sis -s script -o d:\dbax\dbaxreports\ScriptFromScenario.sql --reportoutput d:\dbax\dbaxreports\ReportFromScenario --reportformat html
The script and HTML report files are created using the information provided by the scenario (MelissaSchemaScenario1.sis). It is the same as example 1 (schemacompare with HTML output) from the Examples tab in this article.
Use a Scenario with --targetconnectionstring in Command Line
This example is the same as the one above, but uses the --targetconnectionstring argument to compare the source side (left) to a target side (right) that differs from the one in the saved scenario. The scenario (MelissaSchemaScenario.sis) uses the AdventureWorksDW2017 database on localhost as the target database, and in the example below it overrides that by specifying the WideWorldImporters database in --targetconnectionstring.
dbaxpress.commandline schemacompare -f d:\dbax\scenarios\MelissaSchemaScenario1.sis -s script -o d:\dbax\ScriptFromScenarioTarget.sql --targetconnectionstring "data source=localhost;initial catalog=WideWorldImporters;integrated security=SSPI;" --reportoutput d:\dbax\dbaxreports\ReportFromScenarioTarget --reportformat html
The script, shown as different from the one that used the scenario default target:
The HTML report shows that the source side (left) used was the AdventureWorks2017 database as defined in the scenario, and the target side (right) used the WideWorldImporters database as specified in the command line:
