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:
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.
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:
More information and documentation is available on the Github page.