Options
Strange Behavior Command Line BATCH file
DBNewbie_2007
Posts: 47 Bronze 3
I have been experiencing a strange problem using the COMMAND LINE and a batch file for the SQL Data Compare, version 6.0.0.1124.
I create a project using the SQL Data Compare GUI, then, in MS Explore on the server,
1) copy the project file
2) Rename the file
3) Open the file in the SQL Data Compare GUI.
4) Modify the DATASOURCES tab database name(s) to the ones I want to sync.
5) Verify the SINGLE TABLE I want to sync is selected and has the appropriate WHERE clause.
6) Select SAVE.
When I run all of the "syncs" in one batch file, a few of the database syncs selected tables which were NOT part of the original list? Needless to say, I lost data when the BATCH file discovered files in the DESTINATION database (DB2) did not existing in the SOURCE database (DB1), so records were deleted.
An example of my BATCH file is as follows:
REM MSM1
"C:\Program Files\Red Gate\SQL Data Compare 6\sqldatacompare" /project:"C:\Documents and Settings\Administrator.SJC\Local Settings\Application Data\Red Gate\SQL Data Compare 6\Dan MSM1 Hist to Kevin MSM1 Hist.sdc" /synchronize /out:"D:\RedGate\MSM1Summary.txt"
REM Rename File
for /f "delims=/ tokens=1-3" %%a in ("%DATE:~4%") do (
for /f "delims=:. tokens=1-4" %%m in ("%TIME: =0%") do (
rename MSM1Summary.txt MSM1Summary-%%c-%%b-%%a-%%m%%n%%o%%p.txt
)
)
REM MSM7
"C:\Program Files\Red Gate\SQL Data Compare 6\sqldatacompare" /project:"C:\Documents and Settings\Administrator.SJC\Local Settings\Application Data\Red Gate\SQL Data Compare 6\Dan MSM7 Hist to Kevin MSM7 Hist.sdc" /synchronize /out:"D:\RedGate\MSM7Summary.txt"
REM Rename File
for /f "delims=/ tokens=1-3" %%a in ("%DATE:~4%") do (
for /f "delims=:. tokens=1-4" %%m in ("%TIME: =0%") do (
rename MSM7Summary.txt MSM7Summary-%%c-%%b-%%a-%%m%%n%%o%%p.txt
)
)
I have since added a /a parameter to every sync, but I was wondering if anyone else has experienced this behavior?
I create a project using the SQL Data Compare GUI, then, in MS Explore on the server,
1) copy the project file
2) Rename the file
3) Open the file in the SQL Data Compare GUI.
4) Modify the DATASOURCES tab database name(s) to the ones I want to sync.
5) Verify the SINGLE TABLE I want to sync is selected and has the appropriate WHERE clause.
6) Select SAVE.
When I run all of the "syncs" in one batch file, a few of the database syncs selected tables which were NOT part of the original list? Needless to say, I lost data when the BATCH file discovered files in the DESTINATION database (DB2) did not existing in the SOURCE database (DB1), so records were deleted.
An example of my BATCH file is as follows:
REM MSM1
"C:\Program Files\Red Gate\SQL Data Compare 6\sqldatacompare" /project:"C:\Documents and Settings\Administrator.SJC\Local Settings\Application Data\Red Gate\SQL Data Compare 6\Dan MSM1 Hist to Kevin MSM1 Hist.sdc" /synchronize /out:"D:\RedGate\MSM1Summary.txt"
REM Rename File
for /f "delims=/ tokens=1-3" %%a in ("%DATE:~4%") do (
for /f "delims=:. tokens=1-4" %%m in ("%TIME: =0%") do (
rename MSM1Summary.txt MSM1Summary-%%c-%%b-%%a-%%m%%n%%o%%p.txt
)
)
REM MSM7
"C:\Program Files\Red Gate\SQL Data Compare 6\sqldatacompare" /project:"C:\Documents and Settings\Administrator.SJC\Local Settings\Application Data\Red Gate\SQL Data Compare 6\Dan MSM7 Hist to Kevin MSM7 Hist.sdc" /synchronize /out:"D:\RedGate\MSM7Summary.txt"
REM Rename File
for /f "delims=/ tokens=1-3" %%a in ("%DATE:~4%") do (
for /f "delims=:. tokens=1-4" %%m in ("%TIME: =0%") do (
rename MSM7Summary.txt MSM7Summary-%%c-%%b-%%a-%%m%%n%%o%%p.txt
)
)
I have since added a /a parameter to every sync, but I was wondering if anyone else has experienced this behavior?
Comments
I'm pretty sure you've hit a known bug in SQL Data Compare 6.0 - the selections you make in the GUI aren't used by the command line; instead, the command line takes the list of objects to include and exclude from any /include and /exclude switches you may pass it.
We've fixed this in version 6.1, which is due to be released in the next few days, whereby any tables you exclude in the GUI will also be excluded when you run the command line version. This will work in addition to any /include and /exclude switches you may use.
Version 6.1 will be a free upgrade if you've got 6.0.
Hope that helps,
Robert
Red Gate
A few of the database(s) that compared tables NOT in the .sdc project files had a comparison done some time ago, where I did a data sync in the opposite direction... i.e., the .sdc file targets ONE database table, DB1 -> DB2, but I did a previous compare (that was never saved as a project) moving some "archive" data from DB2 -> DB1.
This is not true for all of the projects, but definitely for 2 out of the 3 that experienced a problem.
Looking forward to the new release with heavy anticipation!
Just to let you know, 6.1 is now out. You can get this by going to Check for Updates on the Help menu.
Robert
Red Gate