Synchronizing database structure, save SQL script
I have ran into a problem while synchronizing database structure when the synchronization is cancelled for any reason (errors, foreign key constraints,....).
I am using the following PS Script:
<p>$Conn1 = New-DatabaseConnection -ServerInstance 'SQLSRV' -Database 'DB1' -Username 'Usr1' -Password 'Pwd1'</p><p>$Conn2 = New-DatabaseConnection -ServerInstance 'SQLSRV' -Database 'DB2' -Username 'Usr2' -Password 'Pwd2'</p><p>$Options = 'DecryptPost2KEncryptedObjects, DisableAndReenableDdlTriggers, ObjectExistenceChecks, -ForceColumnOrder, IgnoreUsersPermissionsAndRoleMemberships, IgnoreUserProperties, IgnoreWhiteSpace, UseCompatibilityLevel, IgnoreDatabaseAndServerName, IgnoreIdentitySeedAndIncrement, IgnoreStatistics, IgnoreFillFactor, IgnoreFileGroups, IgnoreWithElementOrder'</p><p><br></p><p>$myFilterPath ='D:\SYNC_SQL\SYNC_FILTER.scpf'</p><p><br></p><p>$syncResult = Sync-DatabaseSchema -Source $Conn2 -Target $Conn1 -FilterPath $myFilterPath -SQLCompareOptions $Options -AbortOnWarningLevel None -QueryBatchTimeout 300</p><p><br></p><p><b>$syncResult.UpdateSql | Out-File -FilePath 'D:\SYNC_SQL\SYNC_SQL.sql'</b></p>
When there is an error while synchronizing I dont know the object or table where the error occured.
So, I have an option to save the SQL synchronization script to file, this is where the problem appears, this script is only generated when the whole synchronization is succesfull.
If the synchronization is succesfull I don't need the script, I only need it when its NOT succesfull.