How can we help you today? How can we help you today?

One way sync in batch file

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.
kobylka
0

Comments

1 comment

Sort by
  • Brian Donahue
    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
    Brian Donahue
    0

Add comment

Please sign in to leave a comment.