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 a setting to create the whole DDL script, rather than just 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

This Final Automatic 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

Note: I do not want to utilize Always-Recreate database, just want to drop all objects Tables, Sprocs, Views in database. I don't want to lose our data or reconfigure logins. Database Project to Drop Database before deploy?

This is for automatic deployment process for devops, we just want to redeploy and populate in same database everyday 2 am after hours.

  • If you are dropping the stored procedure then you need to use CREATE PROCEDURE not ALTER PROCEDURE. – Chetan Mar 27 '19 at 02:13
  • Out of interest why do you want to drop all and create from scratch? – Dale K Mar 27 '19 at 02:16
  • want to validate if our newest code in Git, can repopulate the database from scratch –  Mar 27 '19 at 02:17
  • For validation why not just create a fresh database somewhere else? – Dale K Mar 27 '19 at 02:18
  • @DaleBurrell this is an automatic deployment process for devops, we just want to redeploy and populate in same database everyday after hours –  Mar 27 '19 at 02:18
  • That doesn't explain why you need a complete drop though, the scripts it generates are designed to keep your actual database schema in sync with the database project without dropping everything. Its not clear to me what you gain doing it from scratch? – Dale K Mar 27 '19 at 02:21
  • we are having schema differential issues when deploying, should have pasted the error log, just curious if functionality exists in vs publish profile like redgate, –  Mar 27 '19 at 02:22
  • And if you really do want a clean start, then its not clear to me why not drop the entire database and start again? – Dale K Mar 27 '19 at 02:22
  • not my call this is against our company policy, DBAs dont want databases dropped, reconfigure filegroups, userlogins, etc –  Mar 27 '19 at 02:23
  • no just wanted clean start, curious if option exists –  Mar 27 '19 at 02:24
  • Well I guess "in theory" you shouldn't need it - cos you shouldn't get schema differential issues if they got their scripts right :) be interested to know what the errors are. – Dale K Mar 27 '19 at 02:27

0 Answers0