The command line uses an xml config file for all comparison options and settings. The xml file allows you to:

  • Specify SQL Server databases, credentials and connection settings
  • Specify the snapshots that contain the database schema
  • Change the comparison options
  • Customize the comparison by excluding objects, setting schema filters or changing object mappings
  • Specify filegroup comparison options
  • Specify the files for the synchronization script, logs, warnings and errors
  • Execute the schema synchronization script

The xml config file contains the following sections:

SectionDescription
Database
   <LeftDatabase>
   <RightDatabase>
Use this element to specify SQL Server databases, credentials or the snapshots containing the schema that should be compared.
Comparison Options
   <ComparisonOptions>
Selected Comparison Options
   <SelectedComparisonOptions>
Excluded Comparison Options
   <ExcludedComparisonOptions>
Use these elements to select or exclude comparison options.
Object Type Selection
   <ObjectTypeSelection>
Use this element to include or exclude database objects by type.
Object Selection
   <ObjectSelection>
Use this element to include or exclude database objects by name.
Schema Filters
   <SchemaFilters>
Schema Filter Expressions
   <SchemaFilterExpressions>
Use these elements to specify schema filters. A schema filter allows you to include or exclude database objects based on matching criteria.
Filegroup Settings
   <FileGroupSettings>
Use this element to specify the filegroup options.
Filestream Filegroup Settings
   <FileStreamFileGroupSettings>
Use this element to specify the filestream filegroup options.
Object Mappings
   <Mappings>
Use this element to specify the mapping type for various database objects. Command line supports mappings by name or schema and name.
Command Line Settings
   <CommandLineSettings>
Use this element to specify various comparison settings such as the output files, the target database, whether databases should only be compared or compared and synchronized, and more.

The following is a typical xml config file:

<?xml version="1.0" encoding="utf-8" ?>
<CommandLineParameters xmlns="http://www.xsql.com/sqlschemacmd.xsd">
     <LeftDatabase>
          <SqlServer>(local)</SqlServer>
          <DatabaseName>Left_DB</DatabaseName>
          <TrustedConnection>true</TrustedConnection>
     </LeftDatabase>
     <RightDatabase>
          <SqlServer>(local)</SqlServer>
          <DatabaseName>Right_DB</DatabaseName>
          <TrustedConnection>true</TrustedConnection>
     </RightDatabase>
     <CommandLineSettings>
          <SchemaScriptFile>script.sql</SchemaScriptFile>
          <SchemaLogFile>log.txt</SchemaLogFile>
          <SchemaWarningsFile>warnings.txt</SchemaWarningsFile>
          <ErrorLogName>error.txt</ErrorLogName>
          <CompareSchema Direction="RightAsLeft" Synchronize="true"></CompareSchema>
     </CommandLineSettings>
</CommandLineParameters>

This config file forces the command line to perform the followings:

  • It compares database Left_DB and Right_DB on the local SQL Server instance. A trusted connection is used for both databases.
  • It saves the synchronization script to the file script.sql indicated by the <SchemaScriptFile> element.
  • It saves the synchronization log to the file log.txt indicated by the <SchemaLogFile> element.
  • It saves any warnings that are generated during the comparison to the file warnings.txt indicated by the <SchemaWarningsFile> element.
  • It saves errors that might occur during the execution of the synchronization script to the file error.txt indicated by the <ErrorLogName> element.
  • It sets the Right_DB as the target database indicated by the attribute Direction="RightAsLeft" under the element <CompareSchema>. The command line will generate a script that makes the Right_DB the same as the Left_DB.
  • It executes the synchronization script and makes permanent changes in target database indicated by the attribute Synchronize="true" under the element <CompareSchema>. By default the command line generates the synchronization script, but does not execute it.

The following command performs a schema comparison using the settings in config.xml:

xSQLSchemaCmd config.xml

The following command validates the file config.xml:

xSQLSchemaCmd config.xml /v

The xml elements and attributes are case-sensitive.

 

 

Total compare from data to schema with IDERA SQL Comparison Toolset. Learn more > >
  • No labels