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
Best Answer
This is accomplished through the
CreateNewDatabasecommand line publish parameter:You can also set this in the publish xml file:
Or you can use the publish UI (which sets the above mentioned XML property):
I consider the documentation on this to be a little hard to find, so here's a link to it: Microsoft Docs - SqlPackage.exe