Visual Studio 2008 Database Project Generating ALTER DATABASE During Schema Comparison

Question

I have a new Visual Studio 2008 database project (Data Dude). It was created by listing our existing database. I made a few changes (new columns, tables, indexes, etc.), and I'm trying to create a deployment (diff) script for deployment. I have a schema comparison setup to perform a comparison and create a diff script file. I think I changed most of the comparison options and the object ignores what I need, however, at the top I get a few ALTER DATABASE commands that I probably would not have created. 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 would prefer to configure the settings so that I don’t have to communicate with my group of 15 people, that they need to delete these lines from the difference file every time they want to pull out and deploy their last environment.

What settings control this?

Answer 1

There are settings for managing this in the project files .sqldeployment and .sqlsettings (available in the Project Properties folder in Solution Explorer). The settings themselves can be changed in the .sqlsettings file [ Screen Parameter Settings "and the ability to disable all script database properties can be found in the first box when viewing the .sqldeployment parameters. [ SQL Deployment Settings Screenshot ]

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.