Red Gate forums :: View topic - command-line to compare and update one database from another
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare Previous Versions
SQL Data Compare Previous Versions forum

command-line to compare and update one database from another

Search in SQL Data Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
mtmazzitello



Joined: 17 May 2010
Posts: 2
Location: Bloomington, MN

PostPosted: Tue Aug 30, 2011 4:05 pm    Post subject: command-line to compare and update one database from another Reply with quote

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.
Back to top
View user's profile Send private message MSN Messenger
aolcot



Joined: 28 Jun 2011
Posts: 25

PostPosted: Tue Aug 30, 2011 4:29 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
mtmazzitello



Joined: 17 May 2010
Posts: 2
Location: Bloomington, MN

PostPosted: Tue Aug 30, 2011 4:41 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message MSN Messenger
aolcot



Joined: 28 Jun 2011
Posts: 25

PostPosted: Tue Aug 30, 2011 4:49 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group