Scripting options allow you to control the way objects are scripted in the database synchronization script. You can choose, for example, not to script the table change tracking, script the start and end value of a sequence, omit the filegroup clause of database objects and many more.
If you are not sure what options would be best for your scenario, click Restore Defaults, and then use the default options.
Most of the scripting options are self-explanatory, and for those that are not, the description provided in the Comparison Options window is sufficient. However, there are two options that merit special attention:
- Script New Columns As NULL. When the synchronization process requires adding a NOT NULL column on the target database, but no default constraint is specified for that column, SQL Server rejects the new column and the synchronization fails. In such scenarios the best course of action would be to add a default constraint on the source column. If such an action is not possible, you can add the new column to the target database as a NULL ALLOWED, and then, after the synchronization, connect to the target database via SSMS and change that particular column from NULL ALLOWED to NOT NULL.
- Script New Constraints WITH NOCHECK. When the synchronization requires adding new constraints, such as a foreign key or a check constraint, on a table, the existing data may violate those constraints, in which case SQL Server rejects the action and the synchronization script fails. To overcome this obstacle, you can choose to add those constraints the WITH NOCHECK clause first, and then verify if the existing data violates the constraints. When this option is ON, Schema Compare generates the following two synchronization scripts which are executed one after the other:
- Main synchronization script. Creates the new constraints with NOCHECK so that no data violation occurs and databases are synchronized successfully.
- Constraints enabling script. Enables the constraints that were created WITH NOCHECK and runs after the main script, on a separate transaction. If the existing data violates these constraints, then the script fails, but the failure does not affect the main database synchronization. In a case of failure, you must address the constraint violations on a case-by-case basis.
