How can we help you today? How can we help you today?

Synchronizing database structure, saving SQL script problem

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.

dUros
0

Comments

2 comments

Sort by
  • way0utwest
    You might try this in SQL Compare and look for an issue with the objects.
    way0utwest
    0
  • 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).
    dUros
    0

Add comment

Please sign in to leave a comment.