I’ve posted previously about deploying SQL database schema changes with MSDeploy but glossed over deploying custom SQL scripts. That post explained how to extend MSBuild for your deployments but its even easier now with the release of the MSDeployAllTheThings Nuget packages:
Setup
The basic steps to setup a SQL script deployment are as follows:
1 – Create a class library in your solution to act as deployment project
2 – Add the MSDeployAllTheThings.SQL Nuget package
3 – Create a folder (or folders) to the deployment project and add your custom SQL scripts
4 – Alter the [projectname].spp.targets file to deploy your custom scripts
You can add multiple DbDeployments nodes with a mix of DACPAC and script references if needed.
Packaging
With the deployment project created, all you have to do is add the following MSBuild flag to your TFS or commandline build to create an MSDeploy package:
/p:DeployOnBuild=true
More Details
More information and documentation is available on the Github page.
https://github.com/rschiefer/MSDeployAllTheThings.Nuget
https://github.com/rschiefer/MSDeployAllTheThings.Nuget/wiki
Happy Deploying!