Configure your project to output a re-usable deployment package during build, which can be deployed onto all of your SQL Server environments using PowerShell or from the command line.

SQL Change Automation's SQLCMD packages contain all the logic needed to determine whether any migration scripts need to be applied and, if so, execute them batch-by-batch, or simply exit if the database is already up-to-date. If any errors occur during deployment, then the deployment will be immediately halted and the transaction will be rolled back. Additionally, the database will be automatically created on the SQL Server on-premise or Azure SQL instance if it does not exist yet.

Initial project setup

Firstly, edit the .sqlproj file for your project and set the GenerateSqlPackage property to true.

Perform a build of the solution.

In the output window, you'll notice that a package file is now being created as part of the build (in this case, a file called AdventureWorksAzure_Package.sql is being created):

Build Artifacts

Upon successful build, the following artifacts will be output to the .\bin\Debug folder:

• <ProjectName>_Package.sql: SQLCMD package deployment script. This SQL file, combined with the PS1 file, can be used to deploy your database to all of your environments
• <ProjectName>_DeployPackage.ps1: PowerShell deployment script. This file accompanies the SQLCMD package file and can be used to execute the database deployment from the command prompt (see below)

Deploying from the Command Line

To deploy the generated SQLCMD package artifact to an instance of SQL Server using the Windows command line, use the following syntax:

@powershell -NoProfile -ExecutionPolicy unrestricted -Command "& { $DatabaseServer='<DatabaseServer>'; &'<OutputPath>\<ProjectName>_DeployPackage.ps1' }"

For example:

@powershell -NoProfile -ExecutionPolicy unrestricted -Command "& { $DatabaseServer='MYDBSERVER'; &'C:\source\samples\AdventureWorksAzure\bin\Debug\AdventureWorks_DeployPackage.ps1' }"

Deploying from PowerShell

To deploy the generated SQLCMD package using PowerShell directly, use the following syntax:

& { $DatabaseServer='<DatabaseServer>'; &'<OutputPath>\<ProjectName>_DeployPackage.ps1' }

For example:

& { $DatabaseServer='MYDBSERVER'; &'C:\source\samples\AdventureWorksAzure\bin\Debug\AdventureWorks_DeployPackage.ps1' }