Visual Studio 2008 Database Project generating ALTER DATABASE during schema compare

Question

I have a new Visual Studio 2008 Database Project (Data Dude). It was generated by pointing at our existing database. I have now made a few changes (new columns, tables, indexes, etc) and I am trying to generate the deployment (diff) script for deployment. I have a Schema Comparison setup to do the comparison and generate the diff script file. I think I've tweaked most of the comparison settings and object ignores to what I need, however, at the top I get a few ALTER DATABASE commands that I'd rather not have generated. They look like this:

IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET PAGE_VERIFY NONE 
            WITH ROLLBACK IMMEDIATE;
    END


GO

I'd prefer to tweak the settings so that I don't have to communicate to my 15+ member team that they need to remove those lines from the differencing file each time they want to pull down and deploy the latest to their environment.

What settings are controlling this?

Answer 1

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"

Click here to view screenshot

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

Click here to view screenshot

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.

Answer 2

There are settings to control this in your project's .sqldeployment and .sqlsettings files (available in your project's Properties folder in Solution Explorer). The settings themselves may be tweaked in the .sqlsettings file [DB Settings Screenshot] and the ability to disable the whole database properties script generation can be found in the first checkbox when viewing the .sqldeployment settings. [SQL Deployment Settings Screenshot]