This page gives an 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 deploy WidgetTest (the target) with WidgetScripts (the source).

For more information, see

• Working with scripts folders
• Continuous integration for databases using Red Gate SQL tools (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.

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 deployment script used to deploy the changes, type:

sqlcompare /scr1:"C:\Scripts\WidgetScripts" /db2:WidgetTest
     /o:Default
     /Report:"C:\SchemaDiffReport.html"
     /ReportType:Interactive
     /ScriptFile:"C:\SchemaSyncScript.sql"
     /sync

Explanation

• /scr1:"C:\Scripts\WidgetScripts" specifies WidgetScripts as the source
• /db2:WidgetTest specifies WidgetTest as the target
• /o:Default specifies that the default SQL Compare options will be used for comparison and deployment
• /sync deploys the data, making WidgetTest the same as WidgetScripts
• /v > "C:\SchemaDeploy.txt" directs detailed command line output to a file
• /Report generates a report of the schema differences and writes it to the file specified
• /ReportType specifies the format of the report
• /ScriptFile saves a copy of the SQL script used to migrate the changes

Deploying the data

To deploy the data and create a basic report, at the command prompt type:

/sqldatacompare /scr1:"C:\Scripts\WidgetScripts" /db2:WidgetTest
     /o:Default
     /Exclude:table:WidgetPurchases
     /sync /v > C:\DataDeploy.txt

To save a copy of the deployment script used to deploy the changes, type:

/sqldatacompare /scr1:"C:\Scripts\WidgetScripts" /db2:WidgetTest     
	/o:Default
     /Exclude:table:WidgetPurchases
     /ScriptFile:"C:\DataSyncScript.sql"
     /sync /v > C:\DataDeploy.txt

Explanation

• /scr1:"C:\Scripts\WidgetScripts" specifies WidgetScripts as the source
• /db2:WidgetTest specifies WidgetTest as the target
• /o:Default specifies that the default SQL Data Compare options will be used for comparison and deployment
• /sync deploys the data source, making WidgetTest the same as WidgetScripts
• /v > "C:\DataDeploy.txt" directs detailed command line output to a file
• /exclude:table:WidgetPurchases excludes WidgetPurchases. All other tables will be deployed.
• /ScriptFile saves a copy of the SQL script used to migrate the changes

Automating the deployment

To automate this deployment, save the whole command line script as a .bat file:

cd "C:\Program Files\Red Gate\SQL Compare 8"
sqlcompare /scr1:"C:\Scripts\WidgetScripts" /db2:WidgetTest
     /o:Default
     /Report:"C:\SchemaDiffReport.html"
     /ReportType:Interactive
     /ScriptFile:"C:\SchemaSyncScript.sql"
     /sync
cd "C:\Program Files\Red Gate\SQL Data Compare 8"
sqldatacompare /scr1:"C:\Scripts\WidgetScripts" /db2:WidgetTest
     /o:Default
     /Exclude:table:WidgetPurchases
     /ScriptFile:"C:\DataSyncScript.sql"
     /sync /v > C:\DataDeploy.txt

You can then schedule deployment using the Microsoft Windows Scheduled Task wizard.

Additionally, you can automatically update the scripts in source control with the changes from the development database by using the development database as the source for the deployment (/db1:WidgetDev) and a scripts folder as the target (/scr2:WidgetScripts).