Synchronizing database structure, saving SQL script problem

dUros

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>$myFilterPath =
'D:\SYNC_SQL\SYNC_FILTER.scpf'</p>

<p>$syncResult =
Sync-DatabaseSchema -Source $Conn2 -Target $Conn1 -FilterPath $myFilterPath
-SQLCompareOptions $Options -AbortOnWarningLevel None -QueryBatchTimeout 300</p>

<p>$syncResult.UpdateSql | Out-File
-FilePath 'D:\SYNC_SQL\SYNC_SQL.sql'</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.

way0utwest

You might try this in SQL Compare and look for an issue with the objects.

dUros

Thank you for reply.

I have install SQL Compare just on my PC on development network. On other servers on other networks I use SQL Change Automation, before I use SDK. It's not many problems but when appears SQL script is crucial. Problems appears mostly because of the data, which was different as data on my system. And not all servers are sync every time so SQL script are different on different servers (DB's).