command-line to compare and update one database from another

[mtmazzitello]

I want to create a command line application that will use SQL Compare to either execute a .sdc file or whatever code is necessary to compare database B with database A and update database B with any differences detected. The idea is that I want database B to be updated to be an exact replica of database A whenever the command line application is executed. The eventual goal is to schedule the command line application to execute at certain intervals so the entire thing is automated.

Has anyone out there done this? Are there any examples anywhere of a visual studio project or even just a powershell or VBScript that show how to use the API to build such an application?
I found an example of a command line SQL Data Compare bat file, but it does not do the update part of the process.

Any ideas, examples, or suggestions would be appreciated.

[aolcot]

hi. Yes this is all perfectly possible by executing SQLCompare.exe from the command line which you could wrap within powershell or a multitude of other tools... You could wrap it up within a console app, but it would be better within powershell i feel.

To find out the options that SQLCompare offers you could run the following:

sqlcompare /?

or to get more verbose information:

sqlcompare /v /?

Here is some more information regarding the use of the command line.

http://www.red-gate.com/supportcenter/List?t=API_CommandLine&p=SQL%20Compare

Example usage from our CI. This syncs a database from a scripts folder which could just as easily be a database instead:

sqlcompare /scr1:"C:\CI\Sql Server\Databases\Development" /s2:SQLSvr /DB2:MyDb /exclude:role /exclude:user /o:n /q /f /sync

Hope this helps

[mtmazzitello]

Thanks for the Reply. I meant to say SQL DATA Compare, not SQL Compare.

My goal is to keep data updated in database B so it matches database A (which will have the identical schema).
I have found examples of using a command line for SQL Data Compare at
http://www.red-gate.com/supportcenter/Content?p=SQL%20Data%20Compare&c=SQL_Data_Compare/help/9.0/SDC_CL_ExamplesUsingTheCL.htm&toc=SQL_Data_Compare/help/9.0/toc1419869.htm
but this is just comparing one table to another.

I would like to automate an entire database data compare/sync, ideally using a defined .sdc file that has the table & key mappings defined etc. but if I have to hand code each table and its primary key into the script I guess I can do that also.

[aolcot]

Sorry!. i should have realised with the .sdc file extension that you referred to.

you can still get the options from the command line using:

SQLDataCompare.exe /?
or
SQLDataCompare.exe /v /?

Here is an example command line with data compare that we used to use to sync the data. I did find it initially a bit fiddly to get working, hence the extra options. You may not need these depending on your schema.

.\sqldatacompare /scr1:"C:\CI\Sql Server\Databases\Production" /s2:SqlSvr /DB2:MyDb /sync /o:d /o:fc /o:t /o:k /f

You could use a project file and specify the name of it with the /pr option e.g. sqldatacompare /pr:"myproject.sdc", but I've never done that and elected to do it all through the options available in the command line.

the critical parameter is the /sync option which is what forces it to perform the update.

hope this helps.