How can I stop SQL CLR projects from generating DROP DATABASE in their deployment scripts?

Question

How can I stop SQL CLR projects from generating DROP DATABASE in some deployment scripts?

I have been using SQL Server SQL CLR for 10+ years for scalar functions, aggregates and User-Defined Types. Early on, the T-SQL scripts generated by a build did what was expected; they deployed the assemblies and objects being built.

But at some point, some of the generated T-SQL scripts began generating DROP DATABASE commands! (I'll try not to repeat too many times how lunatic an idea this is).

Today I found a 10-year-old proposed answer on stackoverflow that didn't actually solve the poster's problem, so I'm asking again.

I'm using Visual Studio 2022, deploying to SQL Server 2016. There is one file of concern in particular, with a name like 'projectname_Create.sql'. Here's the offending section:

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 database $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)] COLLATE SQL_Latin1_General_CP1_CI_AS
GO

Another file, with the name "projectname.sql", just goes about the business of altering the assembly:

USE [$(DatabaseName)];
GO
PRINT N'Altering Assembly [ConcatenateVS2022SS2016]...';
GO
ALTER ASSEMBLY [ConcatenateVS2022SS2016]
    DROP FILE ALL;
GO
ALTER ASSEMBLY [ConcatenateVS2022SS2016]
    FROM 
    0x<<<binary for assembly>>>

It would be great to read any documentation on when these two files are actually used; it could be that the project_create file is never executed by Visual Studio, but "I live in fear" that someday Visual Studio will drop a database when I deploy or build.

Answer 1

Here is the documentation that discusses these options:

https://learn.microsoft.com/en-us/sql/ssdt/database-project-settings

 
The "Create" script is optional, and generating it is controlled via the following option:

Project Settings tab

Create Script (.sql File)

Specifies whether a full .sql CREATE script is generated for all the objects in the project, and placed in the bin\debug folder when the project is built. You can create an incremental update script using the Project Publish command or SQL Compare utility.

 
The non-"Create" script is the incremental script used by SSDT to publish/deploy any changes. It is possible to force the incremental script to also re-create the database via the following option (disabled by default):

Debug tab

Always re-create database

Specifies whether to drop and recreate the database instead of performing an incremental upgrade. You might want to select this check box if you want to run database unit tests against a clean deployment of the database, for example. If this check box is cleared, the existing database will be updated instead of dropped and re-created.

 
I don't recall exactly when SSDT started generating both scripts, but I believe it started prior to Visual Studio 2015. So, it has been around for awhile, but it should not be a cause for concern.