Quick note: Redgate SQL Data Compare using the command line

compareI’ve been working on an automated data compare process for one client.  I’ve been struggling a little with odd hangups and errors with the SQL Data Compare command line interface, but one particular StackOverflow post solved my final problem. So here’s the final solution:

  1. Create a data compare project using the Redgate GUI.
  2. Run the compare – it must pick up at least one difference.
  3. When the results are shown, “check the tickbox between the 2 “Object Name” columns” (thanks again, StackOverflow guy!)
  4. Save your project.
  5. The command to run this compare will have the form
    <filepath>\sqlcompare.exe”
    /project:”<project filepath and name>”
    /scriptfile:<output file for sql script>
    /include:identical
    /force
    /export:<export directory>

A note about the some elements:

  • <filepath> is the path to wherever sqlcompare.exe lives. On my computer, it’s “C:\Program Files (x86)\Red Gate\SQL Data Compare 10\sqldatacompare.exe”
  • <project filepath and name> is the location of your saved SDC file. For example, “c:\Data Compare\DB1 vs DB2.sdc”
  • <output file for sql script> is to create a new file with the SQL change migration scripts.
  • /include:identical “means the compare will not report an error if no changes are detected”.
  • /force will overwrite the sql script.
  • /export:<export directory> will exports the results as .csv files to the specified directory.

In my case, the full command looks like this:

“C:\Program Files (x86)\Red Gate\SQL Data Compare 10\sqldatacompare.exe” /project:”c:\Data Compare\DB1 vs DB2.sdc” /scriptfile:deploy_MyDBName.sql /include:identical /force /export:c:\temp\DB1vsDB2\

You can get additional options and explanations by running sqldatacompare.exe /?

Happy days,
Jen McCown
MidnightDBA.com/Jen
MidnightSQL Consulting