Apply SQL Data Compare command line Switches

JohnnyS

We are using the Bamboo add-on for SQL Change Automation (SCA) to automate our deployment of changes to static data. Literally, we are taking a development DB (which SCA copies into a temporary DB) and comparing it to a Target (our QA environment for example) to get a script to run against QA to make it look just like dev. I have done this manually using SQL Data Compare, but we are now looking to automate thru SCA/Bamboo/Octopus.

The problem is that we are comparing Static Data and some of the data in the Target needs to be preserved. In SQL Compare, we would ignore a specific table using /Exclude:<tablename> and in SQL Data Compare's UI (uncheck the Target boxes) and in the SQL Data Compare command line (using /Exclude:Additional), but we can't find the place to incorporate this command line switch in SQL Change Automation. We tried adding "/Exclude:Additional" in the 'SQL Data Compare options...' section of the Bamboo build configuration, but that errored since switches are not the same as options.


Where do we define switches to use for SQL Compare and SQL Data Compare in SQL Change Automation?

Pete_Ruiz

Hi @JohnnyS

As mentioned in the Support Ticket we have open for you, the comparison command line switches are not supported in SQL Change Automation. So sorry. It would be best to use the sqldatacompare.exe in your pipeline in this case. Also, for the record, a SQL Toolbelt license is required if using SQL Compare or SQL Data Compare part of an automated process. Also, each user that is adding changes to this process should have a license of the SQL Toolbelt.

To include the command line .exe In your Bamboo/Octopus Deploy pipeline, you would just need to add a command line or PowerShell task that calls on the sqldatacompare.exe that's installed with SQL Change Automation PowerShell and do the comparison/sync using whatever switches you need.

The .exe is installed by default in "C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\ in \SC for SQL Compare and \SDC for SQL Data Compare.

https://documentation.red-gate.com/sc/using-the-command-line

https://documentation.red-gate.com/sdc/using-the-command-line

I hope this helps. Please let me know if you have any further questions.

Kind regards,

Pete Ruiz

kevinw

Hi @Pete_Ruiz, Sorry to jump in on this thread but can you confirm if the same limitation applies to the TeamCity SQL Change Automation plugin?  I can see it supports the compare options, but it does not appear to support command line switches such as /exclude:<Table> as above.  Is using the commandline.exe instead of the plugin the only way to go if I need to make use of command line switches?

Thanks, Kevin