Question:
How to use the SQL Studio Data Compare command line interface? Answer:
Here are some examples for using the SQL Studio Data Compare command line utility:
1. Create a new database level compare project named AdventureWorks with left server SILVER\SQLEXPRESS using the user sa and password p@ss and database AdventureWorks and on the right side we use server GREEN\SQLEXPRESS with integrated security and on database AdventureWorks2.
Code:DataCompare_cmd.exe –as AdventureWorks -sl SILVER\SQLEXPRESS –ul sa –pl p@ss –dbl AdventureWorks –sr GREEN\SQLEXPRESS –isecr –dbr AdventureWorks2
2. Create a new table level compare project named AdventureWorks with left server SILVER\SQLEXPRESS using the user sa and password p@ss and database AdventureWorks for table HummanResources.Employee and on the right side we use server GREEN\SQLEXPRESS with integrated security and on database AdventureWorks2 for table HummanResources.Employee. In this example the schema on both sides is HumanResources.
Code:DataCompare_cmd.exe –as "AdventureWorks Employee" -sl SILVER\SQLEXPRESS –ul sa –pl p@ss –dbl AdventureWorks –schl HumanResources -tbll Employee –sr GREEN\SQLEXPRESS –isecr –dbr AdventureWorks2 –schr HumanResources -tblr Employee
3. Use a saved project named AdventureWorks to sync the tables/databases (in this example databases) to the right side if a selection is made (selection of tables when the project is created). Foreign key tables will be included and tables with no primary keys will be excluded. The generated script is saved to the file AdventureWorks.sql and logs the actions to the file AdventureWorks.log.
Code:DataCompare_cmd.exe –s AdventureWorks –x –log "AdventureWorks.log" –f "AdventureWorks.sql" /SR /SKIP /INCLFK
4. Delete saved project named AdventureWorks.
Code:DataCompare_cmd.exe –ds AdventureWorks
5. Compare all tables in a saved database project named AdventureWorks and generate a report for all tables (including tables that have no differences).
Code:DataCompare_cmd.exe -s "AdventureWorks" /CMP /ALL -rpt "Adventure Works Compare Report.txt"
If the compare is between databases on two different servers using integrated security then the command line must specify the linked server and the credentials for linking. In this case the left server will be linked using the "sa" account.
Code:DataCompare_cmd.exe -s "AdventureWorks" /CMP /LINKL -ul sa -pl P@$$w0rd /ALL -rpt "Adventure Works Compare Report.txt"
The above commands will generate a report with the name "Adventure Works Compare Report.txt" and it will contain something like this:
Code:T000_GENERAL ; Matching: 2 ; Different: 0 ; Missing: 1 ; Additional: 0
T001_EXAMPLE1 ; Matching: 3 ; Different: 0 ; Missing: 0 ; Additional: 0
If the command line utility is called without the /ALL parameter then the report for those same databases will look like this (note the missing information for tables that have no differences):
Code:T000_GENERAL ; Matching: 2 ; Different: 0 ; Missing: 1 ; Additional: 0
|