SQL Server database project functionality In Visual Studio 2019

Question

I have a SQL Server database project created in VS 2019. I'm fairly new in using it and have a couple of questions around deployments and pre-deployment scripts. I noticed another team of ours using a migrations table with their pre-deployment scripts. I created a bunch of pre-deployment scripts and added them to a PreDacpac folder. It's my understanding that you can only have one pre-deployment script file so what is the proper way of including the rest of my scripts on building project and including them in dacpac file?

How do I make sure that any modifications to group of tables that the table data is backed up first before making any modifications to them? Is there a setting for it in the db project?

Also does anyone know where to turn off the setting for inserting code to RaisingError in build script if data exists in table?

Thanks

Answer 1

Pre-deployment scripts

When you add a pre-deployment script the comment at the top explains how to add multiple scripts:

 This file contains SQL statements that will be executed before the build script.   
 Use SQLCMD syntax to include a file in the pre-deployment script.          
 Example:      :r .\myfile.sql      

Basically you have one master file that includes other script files.

Backup database before deployment

This depends on your deployment method. For publishing from Visual studio: Right click the project and select publish. Click the Advanced... button to edit Advanced publish settings. There is an option "Back up database before deployment".

RaisingError in build script if data exists in table?

I'm not sure what you mean by this. But in the advanced publishing settings there is by default a property "Block incremental deployment if data loss might occur". If you uncheck this box publishing will not be blocked when your migrations might cause data loss. If table contains data and you add new columns then migrations wont be blocked even if this button is checked.