What are the challenges you face when working across database platforms? Take the survey

One way sync in batch file

kobylkakobylka Posts: 2
edited March 29, 2008 11:21PM in SQL Compare Previous Versions
Can I exclude from the synchronization all tables that are only in a dest database, when I use SQLCompare from the bach file ? I want not to delete tables only to synchronize the New and Different ones.

Arnost K.


  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Arnost,

    That is a very challenging question! From the API, it would be simply a matter of excluding differences only present in database2. Using the command-line utility, it's not so easy because there is no option to simply exclude only objects present in database2 so they are not DROPped.

    You can, however, run sqlcompare.exe twice to do this; first to determine the objects that you want to exclude and then again to actually synchronize the databases. This batch file does this by running SQLCompare and using the output to construct an XML command file excluding all objects that exist only in the second database. When objects are only present in db2, the characters << are present in the output and it uses this as a filter to zone in on the missing objects. Afterwards, it runs sqlcompare using the XML file constructed from the first comparison result. You may test this against the databases supplied with SQL Compare, WidgetStaging and WidgetProduction.
    @echo off
    REM ====== Enter your server and database names here:=======
    set Server1=localhost\SQLEXPRESS
    set Server2=localhost\SQLEXPRESS
    set Database1=WidgetProduction
    set Database2=WidgetStaging
    REM ======= Setting up ==================
    CD \Program Files\Red Gate\SQL Compare 6
    REM delete tmp command file
    if EXIST command.xml DEL command.xml
    REM ======= Construct an XML command file ===================
    echo ^&lt;?xml version="1.0"?^&gt; &gt; command.xml
    echo ^&lt;commandline^&gt; &gt;&gt; command.xml
    echo ^&lt;server1^&gt;%Server1%^&lt;/server1^&gt; &gt;&gt; command.xml
    echo ^&lt;server2^&gt;%Server2%^&lt;/server2^&gt; &gt;&gt; command.xml
    echo ^&lt;database1^&gt;%Database1%^&lt;/database1^&gt; &gt;&gt; command.xml
    echo ^&lt;database2^&gt;%Database2%^&lt;/database2^&gt; &gt;&gt; command.xml
    REM output a list of SQL objects missing from DB2 to command.xml
    for /f "tokens=1-5 delims=\&#91;.\&#93; " %%F in &#40;'sqlcompare /s1:%Server1% /s2:%Server2% /db1:%Database1% /db2:%Database2%'&#41; do @if "%%I" EQU "&lt;&lt;" echo ^&lt;exclude^&gt;%%F:\&#91;%%G\&#93;.\&#91;%%H\&#93;^&lt;/exclude^&gt; &gt;&gt; command.xml
    REM Omit following line to stop the synchronization!
    echo ^&lt;sync /^&gt; &gt;&gt; command.xml
    echo ^&lt;/commandline^&gt; &gt;&gt; command.xml
    REM ======= Run the command file =============
    sqlcompare /argfile:command.xml
Sign In or Register to comment.