Search code examples
sql-serversql-server-2012publishdatabase-projectsql-server-data-tools

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


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:

Deploy database properties check box

Also, here under advanced publish settings:

Advanced publish settings screenshot

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

Database settings screenshot Database settings from Management Studio

How can I prevent this?


Solution

  • 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).

    Operational settings Miscellaneous settings