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.
· Share on Twitter

Comments

  • 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
    · Share on Twitter
Sign In or Register to comment.