Command Line Deployment (MSBuild)
Published 15 February 2017
This functionality requires MSBuild Tools version 12 or greater.
If you would like to perform deployments of your SQL Change Automation projects directly from the command line, you can use the MSBuild tool to produce a "patch" script from your project and optionally deploy it to a given target SQL Server instance.
This article focuses on using the patch method to deploy your database from the command line.
To read about differences between patch and package deployment scripts see Deployment scripts.
Previewing the script before deployment
Use the following command to create a patch script that contains just the migrations and programmable objects that have yet to be deployed to the database, allowing you to preview the changes before they are applied to the database.
Note that the following command will only cause a script to be output from your project; i.e. it will not be executed against the target database.
C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe <<Project or solution file>> /p:Configuration=Release /p:TargetServer=<<SQL Server Instance Name>> /p:TargetUserName=<<SQL Server Authentication User>> /p:TargetPassword=<<SQL Server Authentication Password>>
- TargetServer (required): The name of the SQL Server instance where the database resides
- TargetUsername / TargetPassword (optional): Credentials for SQL Server Authentication-based connections. If you would prefer to use Windows Authentication instead, simply leave these arguments blank
TargetDatabase (optional): The name of the database for which to produce a patch script. If this argument is left blank then the project name will be used as the database name the SQL Server instance name & database name of the target environment (typically Production)
If you decide to specify a solution file, and that solution contains multiple SQL Change Automation projects, it is highly recommended that you do not specify the TargetDatabase argument, as this will attempt to deploy all of the projects to the same database.
After running the above command, a T-SQL script will be produced in the following location:
<<Project Folder>>\bin\Release\<<ProjectName>>.sql
For example, C:\source\MyDatabase\bin\Release\MyDatabase.sql.
To apply the generated script, it can be executed using the SqlCmd utility (which ships with SQL Server) or within SQL Server Management Studio (ensuring that SQLCMD mode is switched on).
Deploying the database from the command line
Use the following command to apply the migrations and programmable objects that have yet to be deployed to the database.
C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe <<Project or solution file>> /p:Configuration=Release /p:DeploymentMode=DeployOnBuild /p:TargetServer=<<SQL Server Instance Name>> /p:TargetUserName=<<SQL Server Authentication User>> /p:TargetPassword=<<SQL Server Authentication Password>>
Assuming that there are no script validation errors, the above command will instantly deploy your project to the destination database after producing the patch script (without the ability to preview the script before deployment).
However you can still post-view the deployment by viewing the output .sql file from the build process (as mentioned above).
Additional MSBuild arguments
For a full list of MSBuild arguments that SQL Change Automation exposes, see this article on Continuous Integration
