Automation of Data Comparision
Nagaraj125
Posts: 9 New member
Can i automate data comparison between the two tables using red gate data compare 13? If so could you please assist how to automate it
Tagged:
Best Answers
-
AlexYates Posts: 264 Rose Gold 2You'll want to use the SQL Compare command line. There's a getting started page here:
https://documentation.red-gate.com/sc13/using-the-command-line/command-line-basics
There are some simple examples here:
https://documentation.red-gate.com/sc13/using-the-command-line/simple-examples-using-the-command-line
And full documentation of all the command line switches is here:
https://documentation.red-gate.com/sc13/using-the-command-line/switches-used-in-the-command-line
Once you've figured out the command you need, you should be able to schedule that using your automation tool of choice. If you don't have one, sticking that command into a batch file and running it from Windows Scheduler would do what you ask.
But I'd love to know exactly what are you trying to achieve? Depending on the answer you may be better off looking at SQL Change Automation.Alex Yates
DevOps Mentor and Coach
Director of DLM Consultants
Creator of Speaking Mentors
Microsoft Data Platform MVP
Friend of Redgate
Twitter / LinkedIn -
AlexYates Posts: 264 Rose Gold 2My humble apologies. I misread the question and sent you the SQL Compare (schema comparing) command line docs rather than the SQL Data Compare (data comparison).
https://documentation.red-gate.com/sdc13/using-the-command-line/command-line-syntax
There are some simple examples here:
https://documentation.red-gate.com/sdc13/using-the-command-line/examples-using-the-command-line/simple-examples-using-the-command-line
It might be because you are cd'ing to the SQL Compare directory but then calling SQL Data Compare (which lives in a different directory).
Try adding the path to SQL Compare / Data Compare to your PATH environment vartiable. That should negate the need for all the CD stuff. Also, have you tried the syntax:
sqldatacompare /project:"C:\Users\UserName\Desktop\Project.sdc" >> "D:\Results\log.txt"
Beyond that, it would help if you could share the error message.Alex Yates
DevOps Mentor and Coach
Director of DLM Consultants
Creator of Speaking Mentors
Microsoft Data Platform MVP
Friend of Redgate
Twitter / LinkedIn -
Alex B Posts: 1,158 Diamond 4Hi @Nagaraj125,
Ah, wait, it's because it's a regular expression for the /Include - it's including everything that contains any of the letters in [JobType] rather than looking for the word JobType.
You'll either want to change it to escape the square brackets like this:<b>/Include:table:\[JobType\] /Verbose >> "D:\Results\log1.txt"</b>
or you'll need to remove them altogether like this:<b>/Include:table:JobType /Verbose >> "D:\Results\log1.txt"</b>
Kind regards,
Alex
Answers
https://documentation.red-gate.com/sc13/using-the-command-line/switches-used-in-the-command-line?_ga=2.261896781.1791667307.1566837276-1706698906.1534783619#Switchesusedinthecommandline-/Report:%3Cfilepath%3E
If this is to set up some sort of automated deployment, remember to use the /synchronise switch:
https://documentation.red-gate.com/sc13/using-the-command-line/switches-used-in-the-command-line?_ga=2.261896781.1791667307.1566837276-1706698906.1534783619#Switchesusedinthecommandline-/Synchronize
However, it sounds like what you want to do is track which changes are made and maybe which changes are associated with the development of this or that feature.
If that's the case, I strongly recommend you consider looking at Redgate SQL Source Control and basing your change management and deployment processes off source control. This will make it much easier for you to track this stuff and deploy with confidence:
https://www.red-gate.com/products/sql-development/sql-source-control/
DevOps Mentor and Coach
Director of DLM Consultants
Creator of Speaking Mentors
Microsoft Data Platform MVP
Friend of Redgate
Twitter / LinkedIn
As mentioned in my last post I wanted to compare tables from two different DB servers.
Hence I used below syntax in my batch file (.bat) but it didn't work out.
C:
/server2:TestServerName /database2:DatabaseName
Also I tried to automate using the already saved project file using below syntax:
sqldatacompare /project:"C:\Users\UserName\Desktop\Project.sdc"
/Out: "D:\Results\log.txt"
But in both the examples it didn't work. Could you please confirm if i am missing anything on the above syntax.
I have created one project and tried to automate the comparison using below syntax in batch file as suggested by u:
sqldatacompare /project:"C:\Users\UserName\Desktop\Project.sdc" >> "D:\Results\log.txt"
However the out file generated from above action in the "Results" folder is not giving any conclusive comparison details. It shows only minimal details.
Attached is the log file generated for your reference. Could you please assist me on the same if i have missed on anything.
Thanks,
Nagaraj S
That doesn't look right to me. I would have expected either some feedback about the results, or some error message. Unless the process was exited before it had finished?
I've emailed the official Redgate support team and asked them to have a look at this thread.
DevOps Mentor and Coach
Director of DLM Consultants
Creator of Speaking Mentors
Microsoft Data Platform MVP
Friend of Redgate
Twitter / LinkedIn
However can you please assist me for the scenario where If I want to compare table (not just the count entire table data) what will be syntex. I am trying with below syntax but not much luck:
sqldatacompare /server1:servername\instance /database1:Testdatabase
/server2:servername\instance /database2:UATDatabase
Error says:
The first issue I can see is that you have tried using SqlCompare.exe first from the \SDC\ folder. The last section you have switched to using SqlDataCompare.exe correctly, but then the second issue below comes into play.
The second issue is that you are pasting a command split across multiple lines into the command prompt window, which will run each line on it's own. You will need to have the command be one continuous line OR alternatively you will need to use the caret character (Shift+6) to span the command across multiple lines in the command prompt when you paste it in. If you do this, you will see the word "More?" at the left of the screen:
So your command would look like:
I hope that helps!
Kind regards,
Alex
Have you visited our Help Center?
Thanks for looking into this.
I tried with above mentioned syntax with table name used as "JobType". But got the error in log file as attached. Kindly suggest on the same.
Looking at the command I put above, I realize that I've left a space between the "/Include:" and "table:[Products]" which may be causing this. If you change the last line of the command above to:
Does it then work? If not, I'd need you to put the whole command you are using (obfuscating any passwords) so I can see what else may be going on.
Kind regards,
Alex
Have you visited our Help Center?
Thanks It worked. But I wanted to compare only one table called 'Jobtype'. But even though i used Include command it compared all the tables available in the database.
Also I wanted to comparison of actual table data not just the count. How to accomplish that? Please suggest me to actual data comparison of the table data.
Below is command used and attached is the result log.
sqldatacompare /server1:BANVS-DEVDB12-3\SQL2012 /database1:BCR ^
/server2:BANVS-DEVDB12-3\SQL2012 /database2:BCRDev ^
/Include:table:[JobType] /Verbose >> "D:\Results\log1.txt"
Thanks again. Now I got out put related to only one table.
But this time also I got output related to only the count of that table. I am looking for complete table comparison which includes table data and expecting the differences in the log file (same as when table is selected for data comparison in SQL Data Compare 13 UI) will be generated.
Could you please help with me this as well.
The individual differences are not written to the console (I believe due to the potential to be quite numerous).
You will need to include the /Export:<directory> switch (see this page) which will create comparison files - one called "Results Summary.csv" which has the count information and the others are listing the actual differences in each table involved (only that have differences I believe).
Kind regards,
Alex
Have you visited our Help Center?
Sorry for the late reply, its been while now.
Can you please suggest where can I use switch command to compare the data of two tables.
This documentation page goes over some simple examples on the command line: https://documentation.red-gate.com/sdc/using-the-command-line/examples-using-the-command-line/simple-examples-using-the-command-line
And this page goes over selecting a single table for comparison:
https://documentation.red-gate.com/sdc/using-the-command-line/examples-using-the-command-line/example-selecting-single-tables-for-comparison
I hope this helps!
Kind regards,
Alex
Have you visited our Help Center?