Options
Sync regardless of Errors
Tim_S
Posts: 1 New member
We have inherited multiple databases of legacy code. We are attempting to sync objects to a dev instance. However some of the source stored procs are not valid on the target system (referencing not existing databases, changed table structures, etc.). We'd like to sync everything that is valid. It seems any failure causes large batches of stored procedures to not be synced. We have tried both the -Force option and -AbortOnWarningLevel None. Are we missing something, or is this not possible with the toolset?
Sync-DlmDatabaseSchema -Source $sourcedb -Target $targetdb -AbortOnWarningLevel None -FilterPath "$filterPath\FunctionStoredProc.scpf"
Sync-DlmDatabaseSchema -Source $sourcedb -Target $targetdb -Force -FilterPath "$filterPath\FunctionStoredProc.scpf"
Sync-DlmDatabaseSchema -Source $sourcedb -Target $targetdb -AbortOnWarningLevel None -FilterPath "$filterPath\FunctionStoredProc.scpf"
Sync-DlmDatabaseSchema -Source $sourcedb -Target $targetdb -Force -FilterPath "$filterPath\FunctionStoredProc.scpf"
Tagged:
Comments
This is by design - it is to protect the user from ending up in a position where only half the changes have been deployed and they need to unpick the mess.
In order to solve your problem I propose two solutions. The first is quicker to implement, the second is more effort but safer:
Option 1: Ignore transactions
Of course, you now lose the benefit of transaction, but this should be a quick and easy way to force the behaviour you want.
SQL Compare has various options that you can set, one of which is NoTransations (or nt). To run Sync-DlmDatabaseConnection with this option you could write a script that looks something like this:
$options = "NoTransactions"
Sync-DlmDatabaseSchema -Source $someScriptsFolder -Target $someDlmDatabaseConnection -SQLCompareOptions $options
Option 2: Filter files
If you would like to only deploy specific objects but you would like to keep the security of transactions you should use filters to either filter the objects out of source control our your deployment. You can filter objects out of source control using the SQL Source Control GUI. To filter the objects out at deployment time you can use -FilterPath to reference a .scpf file. The command is documented here:
https://documentation.red-gate.com/display/DLMA2/Sync-DlmDatabaseSchema
Creating a .scpf file is documented here:
https://documentation.red-gate.com/display/SC12/Using+filters
DevOps Mentor and Coach
Director of DLM Consultants
Creator of Speaking Mentors
Microsoft Data Platform MVP
Friend of Redgate
Twitter / LinkedIn