DBA xPress Command Line

Overview

Overview

The DBA xPress command line feature executes schema and data comparisons that are available in the Workbench 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 SentryOne\Pragmatic Workbench directory. For example, in the 2019.3.1 version of DBA xPress, the full path is:

C:\Program Files (x86)\SentryOne\Pragmatic Workbench 2019.3.1

SentryOne DBA xPress Command Line

The command you execute will start with

dbaxpress.commandline schemacompare

or

dbaxpress.commandline datacompare

Available Arguments

ArgumentDescription
-f or --filepathPath to the scenario file.
-s or --syncDefines the type of operation the sync will perform.

  • script:  Generates a script output
  • direct:  Executes the sync
  • hybrid:  Executes the sync and generates the script output

Note:  Script and hybrid operations require the --scriptoutput option.

Warning: The direct and hybrid options execute update scripts without your review.

-o or --scriptoutputPath to the generated script.
--sourceconnectionstringDefines the connection string of the source side of the comparison or overrides the source connection string defined in the comparison scenario.
--targetconnectionstringDefines the connection string of the target side of the comparison or overrides the target connection string defined in the comparison scenario.
--reportoutputPath 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:
  • HTML
  • HTMLframeset
  • RichText
Note:  HTML is the default format used.

Note:  There is no report generation for datacompare.
--skipwarningsIndicates 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

See the Examples tab for additional examples and walk-throughs.

Examples

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

SentryOne DBA xPress Command Line Schemacompare Script No Differences

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.

SentryOne DBA xPress Command Line Schemacompare Connection Strings

From the example above, the script outputs to d:\dbaxreports\melissascript1.sql and the HTML formatted report goes to d:\dbaxreports\melissareport1.

SentryOne DBA xPress Command Line Schemacompare Script HTML Output

The script file (melissascript1.sql) contains the T-SQL to modify the schema.

SentryOne DBA xPress Command Line SchemacompareConnection Strings Script

The files for the report have been sent to the melissareport1 folder.

Note:  The index.html file contains the report table of contents.

SentryOne DBA xPress Command Line Schemacompare HTML Report

Open the index.html file in a browser to view the report.

SentryOne DBA xPress Command Line Schemacompare Script Viewing HTML Report

Expand the nodes under Contents to view the details.

SentryOne DBA xPress Command Line Schemacompare Script HTML Report Contents

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.

SentryOne DBA xPress Command Line Schemacompare Script RTF Report

Example of the RTF output.

SentryOne DBA xPress Command Line Schemacompare Script Viewing RTF REport

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).

SentryOne DBA xPress Command Line Example of Database in SSMS Before

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.

SentryOne DBA xPress Command Line Schemacompare Hybrid to Find Extra Procedure

The generated statement in the SQL file to drop the stored procedure from the target database:SentryOne DBA xPress Command Line Schemacompare Hybrid TSQL to Remove Procedure for Sync
The HTML-formatted report:SentryOne DBA xPress Command Line Schemacompare Hybrid Differences Report
The database in SSMS showing the altered schema with the stored procedure no longer there:
SentryOne DBA xPress Command Line Schemacompare Hybrid Example of Synced Database

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.

SentryOne DBA xPress Command Line Schemacompare Script Only

Example of the script output.

SentryOne DBA xPress Command Line Schemacompare Generated TSQL Script

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 before DBA xPress can execute these statements.

SentryOne DBA xPress Command Line SQL Server Errors Example

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.
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\dbaxscripts\melissaposterrorsscript1.sql
Note:  There are significantly fewer statements generated (5 KB vs. 1,363 KB file size from Example 2).

SentryOne DBA xPress Command Line Generated TSQL Script after Errors Example

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.
SentryOne DBA xPress Command Line Schemacompare Same Errors in SSMS
Using Scenarios

Using DBA 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 with DBA 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 in DBA xPress:

1. Open Pragmatic Workbench, then select the Schema Inspector (or Data Inspector) tile from the feature finder menu.

Note:  These steps use Schema Inspector.
SentryOne Workbench Available Tiles
2. Enter a Name for your scenario on the Add Comparison Scenario screen, then select Next.SentryOne DBA xPress Schema Inspector Enter Scenario Name
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.
SentryOne DBA xPress Command Line Schema Inspector Set Left Hand Side for Comparison
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.
SentryOne DBA xPress Command Line Schema Inspector Set Right Hand Side for Comparison
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.
SentryOne DBA xPress Command Line Schema Inspector Set Scenario Options for Comparison
6. Once the scenario has been generated, be sure to save it using the Save Scenario or Save Scenario As options at the top.SentryOne DBA xPress Command Line Schema Inspector Save Scenario

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

SentryOne DBA xPress Command Line Using a Scenario

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.

SentryOne DBA xPress Command Line Script Created from Scenario

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

SentryOne DBA xPress Command Line Scenario with Target Override

The script, shown as different from the one that used the scenario default target:

SentryOne DBA xPress Command Line Script Created with Target Override

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:

SentryOne DBA xPress Command Line Report Created with Target Override