Options
Synchronizing database structure, saving SQL script problem
dUros
Posts: 21 Bronze 1
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.
Tagged:
Answers
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).