2

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

2

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.

  • I am right clicking on my sql project and importing an existing database. I am not actually doing the publish or deploy myself. I am then pushing my code into an Azure CICD pipeline which is building my .slqproj, copying the DACPAC files then staging that package into an IaaS dashboard which is used to deploy to the database. The actual deployment to the database is working for me. The issue is before I migrate this to our production environment I need to have the those Alter, Create and Drop Database statements removed. – Jason Aug 29 at 17:57
  • @Jason when you target production, the "schema compare" portion of the DAC deployment will run, and it will see that there is already a database there, and it won't script the ALTER -> DROP -> CREATE statements (as long as that deployment pipeline doesn't set CreateNewDatabase to "True"). – Josh Darnell Aug 29 at 19:44
  • when the process ran in my lower "test" environment there was already an existing database and those statements were generated. I have verified that the CreateNewDatabase is set to False as well. – Jason Aug 30 at 14:51
  • finally got it to work. turns out one of my parameters for only one of my testing environments had the existing database name spelled wrong. easy fixed and too easy to overlook. lol. I appreciate you both for taking the time to address my question. Thanks for the help ! – Jason Sep 3 at 10:05
  • @Jason That's excellent! You're welcome, and I'm glad that you were able to track it down. I hate little issues like that! So hard to catch. – Josh Darnell Sep 3 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.