What are the challenges you face when working across database platforms? Take the survey

Sync regardless of Errors

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"


  • Options
    AlexYatesAlexYates Posts: 264 Rose Gold 2
    edited August 15, 2017 10:10AM
    Sync-DlmDatabaseSchema uses SQL Compare under the hood. SQL Compare wraps the entire update into a single transaction. If there are errors at run time the transaction is rolled back. That is why your changes are not being deployed.

    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:

    Creating a .scpf file is documented here:
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
Sign In or Register to comment.