Options used in the command line
Published 05 June 2013
You can set project configuration options by using the /Options switch.
For example, when mapping objects, SQL Data Compare considers underscores in object names to be differences by default. Therefore, if the objects [dbo].[Widget_Prices] and [dbo].[WidgetPrices] were identical, they wouldn't be mapped, and so couldn't be compared. To successfully compare these objects, use:
SQL Data Compare now treats those objects as identical, and they can be compared.
To specify multiple options, separate the options using commas:
If you don't explicitly set any options, the defaults are used. See Defaults below.
If you do not specify any options, the following default options apply:
- (IgnoreCase) - This option is deprecated, and case insensitive comparison is now the default behavior.
If you want to use these defaults with additional options, specify the default argument and the additional options. For example:
If you don't specify the default argument, only the options you explicitly specify apply.
To specify no options, use the none argument.
Further options are detailed below.
AddDatabaseUseStatement (from v13.4.4)
Adds a USE statement at the top of the SQL deployment script.
Treats object definitions as case sensitive when mapping. For example, Table_A and table_a would not be mapped automatically.
Compresses the temporary files that SQL Data Compare generates while performing the comparison. This makes it less likely that you will run out of disk space when comparing very large databases.
Applies the default options.
DDL triggers can cause problems when you run the deployment. Select this option to disable any enabled DDL triggers before deploying the databases, and re-enable those triggers following deployment.
Disables DML triggers on tables and views before deploying the databases, and then re-enables those triggers following deployment.
Disables foreign keys before deploying the databases, and then re-enables those foreign keys following deployment. Note that in some circumstances foreign keys will be dropped and re-created rather than disabled and re-enabled.
When this option is selected, comment headers are not included in the output script.
Drops primary keys, indexes, and unique constraints before deploying the databases, then re-creates them following the deployment.
If the primary key, index, or unique constraint is the comparison key, it cannot be dropped.
Forces binary collation for all string data types, irrespective of column collation, resulting in a case-sensitive comparison. When this option is selected and the comparison key is a string, this may result in slower performance because the indexes are not used.
New option. Forces any constraints (for example, those on foreign keys) disabled by SQL Data Compare to be re-enabled with CHECK.
This option is deprecated, and case insensitive comparison is now the default behavior.
When mapping objects for comparison, spaces in the names of objects are considered by default. This option ignores spaces in the names of objects, enabling them to be mapped. For example [dbo].[Widget Prices] is mapped to [dbo].[WidgetPrices].
When mapping objects for comparison, underscores in the names of objects are considered by default. This option ignores underscores in the names of objects, enabling them to be mapped. For example, [dbo].[Widget_Prices] is mapped to [dbo].[WidgetPrices].
Includes identity columns in the comparison.
Note that you cannot deploy a view if it includes an identity column.
Includes views in the comparison. Views can be deployed only if the referenced rows are from a single table, and the referenced columns are simple. For example, they must not include identity columns or computed columns.
Includes timestamp columns in the comparison.
Note that timestamp columns cannot be deployed.
You can use this option if you're using SQL Packager 8 to create a package that upgrades a database, and if as part of the upgrade you're adding a new table containing data. When you create the data script to package, the new table won't yet exist, so you need to use this option to specify that the table will exist by the time the data script is run.
For example, you've made changes to the schema and data of a database, WidgetDev, and you're ready to deploy these to WidgetLive. You've made a schema script containing a new table, and you want to make a data script, WidgetDevUpgrade, containing the data for the new table.
sqldatacompare /Server1:MyServer\SQL2014 /Database1:WidgetDev /Database2:WidgetLive /ScriptFile:"C:\Scripts\WidgetDevUpgrade.sql" /options:MissingFrom2AsInclude
To specify no options, use the none argument.
Includes comments in the deployment SQL script.
Re-seeds identity columns so that identity values in the database you are updating match values in the source database.
Uses WITH NOCHECK to skip integrity checks for foreign key constraints. If you select this option, deployments may run faster but foreign keys will be left in a 'not trusted' state.
When this option is selected, SQL Data Compare uses the binary representation of CLR types in the deployment SQL script. If this option is not selected, CLR data types are represented as strings.
When this option is selected, and the data in two columns differs only by the number of spaces at the end of the string, SQL Data Compare treats those columns as identical.
Note that this option does not apply to CLR columns, or XML columns.
When this option is selected, SQL Data Compare performs a checksum prior to comparison. Data is compared only if the checksums differ. You can use this option to improve the performance of SQL Data Compare.
Note that in SQL Server 2000 databases, db_owner permissions are required to use this option.
When this option is selected transactions are used in the deployment SQL scripts, enabling changes to be rolled back if the deployment fails. BEGIN TRANSACTION is inserted at the beginning of the deployment SQL script, and COMMIT TRANSACTION at the end of the script.