CODEWITHSUNDEEP
deploymentvisual-studio-2013sql-server-data-tools
J0e3gan
J0e3gan

Reputation: 8938

SqlPackage.exe ignores its CreateNewDatabase property?

In VS 2013, have two SSDT composite projects that target the same database:

When I deploy the 2nd project, I only want to update the target database as needed, never drop/create it.

To deploy the 2nd project, I am attempting to use SqlPackage.exe as follows to generate a deployment script:

C:\some\directory\ssdt\sqlpackage.exe ^
    /a:Script ^
    /op:".\BlahTest.sql" ^
    /sf:".\BlahTest.dacpac" ^
    /tsn:"localhost" ^
    /tdn:"BlahTest" ^
    /p:CreateNewDatabase="False" ^
    REM ...

I expected /p:CreateNewDatabase="False" to mean that I would not find DROP DATABASE or CREATE DATABASE in BlahTest.sql; but I see the following irrespective of the CreateNewDatabase property (i.e. set to "False", set to "True", or omitted):

USE [master];


GO

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

How can I get SqlPackage.exe to output a script that does not drop/create the target database?

Upvotes: 0

Views: 3766

Answers (1)

Kevin Cunnane
Kevin Cunnane

Reputation: 8110

The CreateNewDatabase flag just determines whether to always do a full drop and recreate when publishing - perhaps the SqlPackage.exe documentation isn't clear enough about this? It states that CreateNewDatabase "Specifies whether the target database should be updated or whether it should be dropped and re-created when you publish to a database." The assumption is that if you're choosing to publish, you want the database to be created no matter what.

You can't do what you want using just SqlPackage.exe but it would be very easy to write your own code to do it. See this answer on how to check if a database exists for sample code checking if a database exists. You could write a powershell script that does this check and calls into SqlPackage.exe if CheckDatabaseExists is true.

Upvotes: 2

Related Questions