How to remove ALTER DATABASE statement from SSDT script publication?

Question

Denial of responsibility. There is a similar question in SO that seems to be related to the old version of SSDT. The selected answer refers to settings files that are not in my project. I am sure that I have equivalent settings in the new project format, correctly configured.

I am new to SSDT, and I still do not believe that I will not change my database inadvertently. Having received the settings the way I wanted, I tried publishing to see what it would try to do with my database. I get these statements added to the publication script:

ALTER DATABASE [$(DatabaseName)] SET ANSI_NULLS ON, ANSI_PADDING ON, ANSI_WARNINGS ON, ARITHABORT ON, CONCAT_NULL_YIELDS_NULL ON, CURSOR_DEFAULT LOCAL, RECOVERY FULL, AUTO_UPDATE_STATISTICS ON WITH ROLLBACK IMMEDIATE; ALTER DATABASE [$(DatabaseName)] SET PAGE_VERIFY NONE WITH ROLLBACK IMMEDIATE; EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)] SET TRUSTWORTHY OFF WITH ROLLBACK IMMEDIATE'; 

I don't want the database project to ever change my database settings, so I'm not sure about Debug settings:

Also here in the advanced publishing settings:

In the "Project Settings" | Database Settings I did everything that matches my database:

How can I prevent this?

Answer 1

My solution was to carefully verify that all settings were perfect. I assumed that since I set some parameters the same, these settings will no longer be in the generated script change. However, it is not. If there are differences in database settings, they appear to include others that have the same value with the wrong value.

The settings that I skipped were on the 2nd and 3rd tabs of the "Database Settings" dialog box ("Operating and Miscellaneous").

Answer 2

There are two checkboxes that must be unchecked before saving the publication profile. Make sure that you go to the project properties → Debugging and uncheck "Deploy database properties"

Click here to view a screenshot.

then right-click on the database project -> publication, then click "Advanced" to uncheck the "Deploy database properties"

Click here to view a screenshot.

Click OK, then Save Profile As, and from now on, every time you deploy the created script using the newly created publish profile, only those changes you want will be contained.

I am using VS 2013 with the latest SSDT from April 20 to 2016.

Answer 3

I have not used SSDT. However, when I use SSMS to generate scripts, I usually just delete the code that I do not want to execute. When I make changes to the database schema, I want it to be written in a script and in source control. Thus, my installations / migrations are repeatable and verifiable. Not sure if this is how you intend to use SSDT, but this is food for thought.