0

I have a database project in Visual Studio Team Services Git, and want to deploy into database on actual server.

(a) Is there setting in publish profile, to drop all objects and recreate?
(b) or is there setting to create whole DDL script, rather than finding schema comparison discrepancy? Want to conduct this from Visual Studio, I know SSMS has option to Generate Scripts for all objects.

Want to conduct for all tables, sprocs, views, not just simple example below. Plan to redeploy and repopulate data warehouse everyday.

Example, say this in Source control database project.

Source Control:

create procedure dbo.SelectTestOne
as 
select 1

And actual server on localdb is

Local Server Discrepancy:

create procedure dbo.SelectTestOne
as 
select 2

Predeployment Script:

If I create a Script.PreDeployment, which drops all objects,

drop procedure dbo.SelectTest

The Final publish profile Script, will still do an alter, instead of recreate. So question is how do I drop all objects and recreate them? (I know Redgate has this option)

drop procedure dbo.SelectTest
GO

GO
PRINT N'Altering [dbo].[SelectTest]...';


GO
ALTER procedure dbo.SelectTest
as 

select 1
GO
PRINT N'Update complete.';


GO
1

This is accomplished through the CreateNewDatabase command line publish parameter:

/p CreateNewDatabase=True

Specifies whether the target database should be updated or whether it should be dropped and re-created when you publish to a database.

You can also set this in the publish xml file:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    ... other properties ...
    <CreateNewDatabase>True</CreateNewDatabase>

Or you can use the publish UI (which sets the above mentioned XML property):

SSDT user interface screenshot

I consider the documentation on this to be a little hard to find, so here's a link to it: Microsoft Docs - SqlPackage.exe

Your Answer

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