Possibly save 1 hour of your time: In your database project, you may set it to build as a dacpac. Then you can use this dacpac to deploy scripts to your database. However, after you setup a post deployment script, it still didn’t seem to execute after you deploy the dacpac to your target database. Here are some guidance:
- If you haven’t already, you can add post deployment script by right click any folder in your project and choose Add >> Script. This script should contain your script or point to another script using notation :r .\myfile.sql. Use doubt quotes to wrap file path/name with space.
- After add script, right click the file and ensure Build Action is set to PostDeploy.
- Now build the project.
- Go to bin folder and find the dacpac. You can right click on this file and unpack it somewhere. Once unpacked, please verify there is a file name postdeploy.sql with the script code inside. This should be it you script will be execute when you deploy your dacpac. Make sure you are targeting the right database and verify using the right database. I got burned once looking at a different staging database.
Command for deploying dacpac:
“C:\Program Files\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe” /Action:Publish /SourceFile:”Your Database.dacpac” /TargetDatabaseName:YourDatabaseName /TargetServerName:localhost /p:ExcludeObjectTypes=”ApplicationRoles;DatabaseRoles;Permissions;RoleMembership;Users;Credentials;ServerRoleMembership;ServerRoles” /p:BlockOnPossibleDataLoss=False