I’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:
- Create a data compare project using the Redgate GUI.
- Run the compare – it must pick up at least one difference.
- When the results are shown, “check the tickbox between the 2 “Object Name” columns” (thanks again, StackOverflow guy!)
- Save your project.
- 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