Sql-server – How to remove Alter, Drop, Create SQL from deployment script created in VS 2015 using SSDT 14.06

configurationsql serversql server 2014ssdtvisual studio 2015

I need to remove the below auto-generated deployment script for my SQL project. I am using VS 2015 with SSDT 14.06. Would this be something that can be set in the Advanced Deployment settings under the Drop tab ? This is for an existing Database deployment. 

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [$(DatabaseName)], FILENAME = 
N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
    LOG ON (NAME = [$(DatabaseName)_log], FILENAME = 
N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE 
SQL_Latin1_General_CP1_CI_AS
GO
USE [$(DatabaseName)];

I have the following items all unchecked for my deployment options in the properties:

— Deploy database properties
— Always re-create database
— Block incremental deployment if data loss might occur
— DROP objects in target but not in project
— Do not use ALTER ASSEMBLY statements to update CLR types

Best Answer

I've only ever seen that code get scripted in two situations:

When the "Always re-create database" advanced publish option is checked.

That option can be set in multiple ways depending on how your deployment process works.

Here it is in the user interface, when you do a one-off publish or save a re-usable ".publish.xml" file:

screenshot of publish settings UI

Without using the UI, you can see this in the publish file XML:

<CreateNewDatabase>True</CreateNewDatabase>

Note that the default is "False", so the absence of the element means false as well

You can also pass this as a command line parameter to sqlpackage.exe:

/p: CreateNewDatabase=True

When the TargetDatabase doesn't exist

If the target database doesn't exist, then that is added to the script for safety reasons to just make sure it doesn't fail on creating the new database.

You'll need to double check that these properties in the publish XML point to a valid database that exists:

<TargetDatabaseName>YourDatabaseName</TargetDatabaseName>
<TargetConnectionString>Data Source=...etc...</TargetConnectionString>

And here it is in the UI:

Screenshot of target database and connection options

If you could share more details about how you generate the publish script (the step-by-step of what you do, e.g. right-click on the SSDT project in Visual Studio, Publish, etc), we can help track down why that's showing up for you.

Related Question