Configure a template file with schema comparison options
When developers run a schema comparison through the command line or PowerShell, they often list all schema comparison options directly in the script. For example:
$process = Start-Process -FilePath $diffToolLocation -ArgumentList "/schemacompare /source connection:`"Data Source=source_server_name;Initial Catalog=source_database_name;Integrated Security=False;Password=$SourcePass;User ID=$SourceUser`" /target connection:`"$TargetConnectionString`" /DisableDependencyAnalysis:No /IgnorePermissions:Yes /QuoteObjectNames:Yes /IgnoreTemporalHistoryTable:Yes /IgnoreColumnOrderInIndexes:No /IgnoreConstraintsNames:No /IgnoreForeignKeys:No /MappingIgnoreSpaces:Yes /MappingIgnoreCase:Yes /MappingSimilar:No /ForceColumnOrder:Yes /IgnoreParserErrors:No /IgnoreCase:Yes /IgnoreComments:No /IgnoreWhiteSpace:Yes /IgnoreObjectNamesInDefinitions:Yes /IgnoreDataTypesInColumn:No /CompareDatabaseProperties:No /UserDataTypesToSystem:No /IgnoreAuthorization:Yes /IgnoreChangeTracking:No /IgnoreCollations:No /IgnoreDataCompression:Yes /IgnoreDatabaseInSynonyms:No /IgnoreDatabaseExtendedProperties:No /IgnoreDatabaseObjectExtendedProperties:No /IgnoreEventNotificationOnQueues:No /IgnoreFilegroupsPartitionSchemes:No /IgnoreFullTextCatalogPath:No /IgnoreFullTextSearch:No /IgnoreNextFilegroups:No /IgnoreSemicolons:No /IgnoreSequenceMax:No /IgnoreSequenceMin:No /IgnoreSequenceStart:No /IgnoreTSQLtFramework:Yes /IgnoreUserDefaultSchema:Yes /PopulateFullTextIndexes:No /IgnoreUserPermissions:Yes /IgnoreUserDefaultLanguage:No /IgnoreLogins:Yes /IgnoreClrDataTypesInColumn:No /IgnoreIdentitySeedIncrementValues:No /IgnoreIdentity:Yes /IgnoreDefaultCollations:Yes /IgnoreBoundRulesDefaults:No /IgnoreLockEscalation:Yes /IgnoreTemporalHistoryTableName:Yes /IgnoreTableDMLTriggers:No /IgnoreInsteadOfTriggers:No /IgnoreDMLTriggerState:No /IgnoreDDLTriggerState:No /IgnoreTriggerFireOrder:No /IgnoreIndexes:No /IgnoreStatistics:No /IgnoreIndexesNames:No /IgnoreStatisticsNorecompute:No /IgnorePadIndexAndFillfactor:No /IgnoreIndexColumnsSortOrder:No /IgnoreIndexPageAndRowLock:No /IgnoreCheckConstraints:Yes /IgnoreDefaultConstraints:Yes /IgnoreNocheck:Yes /IgnoreWithNocheck:Yes /IgnoreConstraintSystemNames:Yes /IgnoreNotNullConstraints:No /IgnoreForeignKeyActions:Yes /IgnorePrimaryKeys:No /IgnoreUniqueKeys:No /IgnoreNotForReplication:No /IgnoreReplicatedTables:Yes /IgnoreReplicationTriggers:No /IgnoreReplicatedProcedures:No /IgnoreReplicatedSchemas:No /IgnoreReplicatedRoles:No /DecryptEncryptedObjects:Yes /IgnoreWithEncryption:No /IgnoreSignatures:No /IgnoreWithOptionOrder:Yes /IgnoreKeywordReduction:Yes /IgnoreQuotedIdentifierAndANSINulls:No /IgnoreDropIndexes:No /IgnoreDropDMLTriggers:No /ExecuteAsSingleTransaction:No /AddingErrorHandling:No /DeployDatabaseInSingleUserMode:No /AddWithEncryption:No /IncludeUseDatabase:Yes /UseSchemaNamePrefix:Yes /IncludePrintComments:No /ExcludeComments:No /AffectedFileReadOnly:Yes /AddWithNocheck:No /DisableDdlTriggers:No /VerifyTableData:No /CheckObjectExistence:No /UseSchemaTransfer:Yes /DropCreateOnly:No /DropCreateOnlyTables:No /DropCreateOnlyAssemblies:No /SynchronizeAsmViaFiles:No /RefreshViews:Yes /CommentDdlForKeysCertificates:No /ExcludeDependencies:No /AddTransactionIsolationLevel:Serializable /report:`"$currentReportFile`" /reportformat:html /log:`"$logName`" /filter:`"$SCFilter`" /sync:`"$diffScript`"" -PassThru -Wait -WindowStyle Hidden -RedirectStandardOutput $DiffScriptLocation
While this works, the script seems to be complicated to read. So, it can be optimized it in a few ways:
- Save the settings in a schema comparison (.scomp) template file using dbForge Schema Compare
- Create an arguments file to include all necessary schema comparison options. This file can then be referenced in the command-line or PowerShell script using the
/argfile:"D:\file_name.txt"command
After the file is created, it can be used to automate the schema comparison process from the command line or PowerShell.
Saving the settings in a .scomp template file
1. On the toolbar, click New Schema Comparison to open the New Schema Comparison wizard.
2. On the Source and Target page of the wizard, specify the source and target details:
- Choose Database as a source and target type
- Select the source and target server connection and databases to be compared
3. Optional: On the Options page, select the comparison options to be applied to the schema comparison and then select Compare.
4. The Schema Comparison document opens, displaying the objects to be synchronized.
On the ribbon, select File > Save [default file name].scomp As to save the *.scomp template file.
5. In the Save As dialog that opens, specify the file name and the path to store the file and then select Save to save it.
After the *.scomp file has been created, it can be used to automate schema comparison and synchronization operations from the command line or PowerShell ISE.
Running schema comparison using the .scomp file
1. Start the Windows Command Prompt.
2. Execute the following compfile command by replacing file_name.scomp with the actual path to the template file containing schema comparison settings:
"C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Schema Compare for SQL Server\schemacompare.com" /schemacompare /compfile:"file_name.scomp" /sync
The result would be as follows:
Creating a command-line arguments file
1. On the toolbar, click New Schema Comparison to open the New Schema Comparison wizard.
2. On the Source and Target page of the wizard, specify the source and target details:
- Choose Database as a source and target type
- Select the source and target server connection and databases to be compared
Click Next.
3. On the Options page, select the comparison options to be applied to the schema comparison and then click Save settings to a command-line arguments file.
4. In the Save As window that opens, select the folder to store the file and specify the name of the file with a *.txt extension.
5. To save the file, click Save.
After the *.txt file has been created, it can be used to automate schema comparison and synchronization operations from the command line or PowerShell ISE.
Running schema comparison using the command-line arguments file
1. Start the Windows Command Prompt.
2. Execute the following argfile command by replacing file_name.txt with the actual path to a file containing the required command-line arguments:
"C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Schema Compare for SQL Server\schemacompare.com" /argfile:"file_name.txt"
The result would be as follows:
