© Red Gate Software Ltd 104 15 July 2009
To compare the data in all tables in two local databases (Windows authentication): Using an XML file: <?xml version="1.0"?> <commandline> <database1>FirstDatabaseName</database1> <database2>SecondDatabaseName</database2> </commandline>
Using the command line:
sqldatacomapre /db1:FirstDatabaseName /db2:SecondDatabaseName
To compare the data in all tables in databases on different hosts:
Using an XML file: <?xml version="1.0"?> <commandline> <database1>FirstDatabaseName</database1> <server1>Hostname1</server1> <database2>SecondDatabaseName</database2> <server2>Hostname2</server2> </commandline>
Using the command line:
sqldatacomapre /db1:FirstDatabaseName /db2:SecondDatabaseName /s1:Hostname1 /s2:Hostname2
To compare the data in all tables in two databases using SQL Server authentication: Using an XML file: <?xml version="1.0"?> <commandline> <database1>FirstDatabaseName</database1> <username1>Username1</username1> <password1>Password1</password1> <database2>SecondDatabaseName</database2> <username2>Username2</username2> <password2>Password2</password2> </commandline>
Using the command line:
sqldatacomapre /db1:FirstDatabaseName /u1:Username1 /p1:Password1 /db2:SecondDatabaseName /u2:Username2 /p2:Password2
To compare the data only in tables whose name contains the word Product:
© Red Gate Software Ltd 105 15 July 2009 <?xml version="1.0"?> <commandline> <database1>FirstDatabaseName</database1> <database2>SecondDatabaseName</database2> <include>Table:Product</include> </commandline>
Using the command line:
sqldatacomapre /db1:FirstDatabaseName /db2:SecondDatabaseName /include:table:\[Product\]
To compare the data only in tables whose name contains the word Product, except for the ProductHistory table:
Using an XML file: <?xml version="1.0"?> <commandline> <database1>FirstDatabaseName</database1> <database2>SecondDatabaseName</database2> <include>Table:Product</include> <exclude>Table:ProductHistory</exclude> </commandline>
Using the command line:
sqldatacomapre /db1:FirstDatabaseName /db2:SecondDatabaseName /include:table:\[Product\] /exclude:table:\[ProductHistory\]
To compare the data in tables using an index as a comparison key
Using an XML file: <?xml version="1.0"?> <commandline> <database1>FirstDatabaseName</database1> <database2>SecondDatabaseName</database2> <comparisonkeys>TableName:IndexName</comparisonkeys> </commandline>
Using the command line:
sqldatacomapre /db1:FirstDatabaseName /db2:SecondDatabaseName /comparisonkeys:TableName:IndexName
To compare a backup file with a database:
Using an XML file:
<?xml version="1.0"?> <commandline>
<backup1>D:\MSSQL\BACKUP\BackupOfFirstDatabase.sqb</backup1> <database2>SecondDatabaseName</database2>
</commandline>
© Red Gate Software Ltd 106 15 July 2009
sqldatacompare /backup1:D:\MSSQL\BACKUP\BackupOfFirstDatabase.sqb /db2:SecondDatabaseName
To retrieve verbose output of the data differences between two databases:
Using an XML file: <?xml version="1.0"?> <commandline> <database1>FirstDatabaseName</database1> <database2>SecondDatabaseName</database2> <verbose/> </commandline>
Using the command line:
sqldatacomapre /db1:FirstDatabaseName /db2:SecondDatabaseName /verbose
To migrate data changes from the first database to the second database:
Using an XML file: <?xml version="1.0"?> <commandline> <database1>FirstDatabaseName</database1> <database2>SecondDatabaseName</database2> <synchronize/> </commandline>
Using the command line:
sqldatacomapre /db1:FirstDatabaseName /db2:SecondDatabaseName /synchronize
© Red Gate Software Ltd 107 15 July 2009
This topic provides a simple example of using the SQL Compare and SQL Data Compare command line interfaces to deploy a database from source control.
In this example, changes have been made to the schema and data of the database WidgetDev. These changes must be deployed to the testing database WidgetTest. The latest version of WidgetDev is maintained in source control as the scripts folder WidgetScripts.
The changes are deployed by using SQL Compare and then SQL Data Compare to synchronize WidgetTest (the target) with WidgetScripts (the source).
For more information, see
•
Working with scripts folders (page 83)•
Continuous integration for databases using Red Gate SQL tools (pdf) (http://downloads.red-gate.com/HelpPDF/ContinuousIntegrationForDatabasesUsingRedGateSQLTools.pdf) Before we can deploy, we must get the latest version of WidgetScripts from source control. In this example, the latest version is updated to the working folder
C:\Scripts\WidgetScripts
We will deploy all changes to the database schema, and changes to only the static data. The transactional data in the table WidgetPurchases will not be modified.
Note that:
•
Scripts folders and the command line interface are only available with the SQL Compare and SQL Data Compare Professional Editions.•
Schema synchronization must therefore be performed first, as data synchronization may fail if the schemas are not identical.Deploying the schema
To deploy the schema and save a basic report of the process, at the command prompt type:
sqlcompare /scr1:"C:\Scripts\WidgetScripts" /db2:WidgetTest /o:Default
/sync /v > "C:\SchemaDeploy.txt"
To create a more readable report of the schema differences and save a copy of the synchronization script used to deploy the changes type:
sqlcompare /scr1:"C:\Scripts\WidgetScripts" /db2:WidgetTest /o:Default
/Report:"C:\SchemaDiffReport.html"