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.

TBH I've only ever seen a single publish script generated. Not multiple scripts with names like projectname_Create.sql. Is this some option to create multiple scripts? Or what are you doing that creates these? Where do you see them? — Martin Smith
– Martin Smith, Commented Jun 12, 2023 at 13:55
There's a project option to have a Create script but it's not selected by default. It's intended, so far as I'm aware, to be used instead of a dacpac to deploy the whole database in pristine form, not to support any incremental deployment features. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 12, 2023 at 13:59
@Charlieface - This USE [$(DatabaseName)] is a normal script for dacpac deployments so something to take up with Microsoft not the questioner here — Martin Smith
– Martin Smith, Commented Jun 12, 2023 at 14:25
Not ideal but you can just add a post or pre deploy script to run arbitrary scripts at publish time anyway so the whole thing does need to be gated with a secure pipeline subject to code review — Martin Smith
– Martin Smith, Commented Jun 12, 2023 at 14:29
I just tested publishing a dacpac with the database name set to master]; SELECT 'SQL Injection possibility'-- and the script generated did escape the closing square bracket :setvar DatabaseName "master]]; SELECT 'SQL Injection possibility'--" (so defeating my SQL injection attempt) — Martin Smith
– Martin Smith, Commented Jun 12, 2023 at 20:20

Solomon Rutzky · Accepted Answer · 2023-06-12 21:22:54Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related