How do I eliminate the ALTER DATABASE statement from my SSDT publish script?

Question

Disclaimer: There is a similar question on SO that appears to be referring to an older version of SSDT. The selected answer references settings files that are not in my project. I believe I have the equivalent settings in the new project format set correctly.

I'm new to SSDT, and I don't trust it yet to not change my database in unintended ways. After getting the settings the way I wanted, I tried a publish to see what it would try to do to my database. I'm getting these statements added to the publish 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 modify my database settings, so I have this unchecked under Debug settings:

Also, here under advanced publish settings:

Under Project Settings | Database Settings I made everything match my database:

How can I prevent this?

Archaism

Are you on the latest SSDT bits? I have the same settings and don't have any ALTER DATABASE commands in my release scripts. Check blogs.msdn.com/b/ssdt and probably be on at least December 2012 or March 2013. (June just came out so may want to exercise caution there.) – Twosided

I'm using the June SSDT. – Archaism

Using the same. If my DB options are the same, they just don't get scripted. In the publish XML code, I have "<ScriptDatabaseOptions>False</ScriptDatabaseOptions>" though my "local" publish is using "True" in there. Once the options are the same, we're good for those. If you right-click the publish file to open it you can see the XML. Maybe check inside that XML to see the settings and tweak manually? Maybe let it run once and see if it goes away on future runs? – Twosided

I'll take a closer look at it then. Maybe I'm just missing one simple thing. – Archaism

Answer 1

My solution was to carefully verify that all settings were a perfect match. I had assumed that since I had set some of the settings to be the same, that those settings would no longer be in the generated change script. This is not the case, however. If there are any database setting differences, it appears to include others that ARE the same with the wrong value.

The settings I had missed were on the 2nd and 3rd tabs of the Database Settings dialog (Operational and Miscellaneous).

Archaism

Interesting... I'll try to reproduce it – Prose

Answer 2

There are two checkboxes that have to be unchecked prior to saving the publish profile. Make sure you go to project properties --> Debug and uncheck "Deploy database properties"

then right click on your database project --> publish then click "Advanced" to uncheck "Deploy database properties"

Click OK, Click Save Profile As, and from now on, every time you deploy your generated script using the publish profile you've just created, will only contain the modifications you want.

I'm using VS 2013 with the latest SSDT as of 20-Apr-2016.

Josiejosler

Answer 3

Actually, you can set the parameter "ScriptDatabaseOptions" to false if you are using sqlpackage.exe to generate the generate script.

Flinger

The command would be "sqlpackage.exe /p:ScriptDatabaseOptions=false" ... – Hostelry

Answer 4

Similar to @yashan's answer, you can set to False the property ScriptDatabaseOptions in the *.publish.xml file of the Database Project in Visual Studio.

  <PropertyGroup>
    <ScriptDatabaseOptions>False</ScriptDatabaseOptions>
  </PropertyGroup>

Siegel

Answer 5

I haven't used SSDT. However, when i use SSMS to generate scripts, I typically just delete the code that I don't want to execute. When I am making changes to DB schema, I want it scripted and in source control. That way my installs / migrations are repeatable and testable. Not sure if this is how you intend to use SSDT, but it is food for thought

Sinecure

My goal is to have our TFS build server automatically deploy database changes when SQL code is checked in to source control as part of a database project. I could probably add a pre-deployment script that would do what you described, but hopefully I can manage it without a hack. – Archaism