SSMS-Tasks-Generate Scripts - add Drop if exists to script

dstarkey

Hello,

Is it possible to add the "Drop if Exists" condition for all of the items procs, tables etc.... before the "Create" portion of the script??

James B

Thanks for your post. This is a very common feature request, and we're hoping it's going to be implemented in version 9; so keep an eye open for an EAP release in the next couple of months.

David Atkinson

dstarkey wrote:

Hello,

Is it possible to add the "Drop if Exists" condition for all of the items procs, tables etc.... before the "Create" portion of the script??

Can I check that you want to put "drop if exists" before a "create table" statement? Are you not worried that that your data would be lost?

David Atkinson
Product Manager
Red Gate Software

einman33

What about for table objects:

IF EXSITS....ALTER TABLE else CREATE TABLE?

David Atkinson

Do you often come across instances when you run scripts where tables sometimes exist and sometimes don't exist in the target? I'd be interested in understanding how situations like this arise so we can better solve the issue.

David

mattkarp

David,

We have a situation where we have to send deployment scripts to existing customers where we cannot see their SQL Server. So we "hope" the schema looks like it did since we last deployed, but we have no way to guarantee that or control that (due to the nature of our clients and product). When we deploy, ideally we'd like to tear down all non-destructive objects (sps, views, functions, etc.) first. For destructive objects, tables, we have to deal with them differently because we have to preserve (and in some cases etl/migrate) the data and obviously this depends on the nature of the change to the table. So in the case of the non-destructive changes we'd like the "drop if exists" feature or an option to wholesale drop all existing objects specified then re-create in the correct dependency order.

einman33

David Atkinson wrote:

Do you often come across instances when you run scripts where tables sometimes exist and sometimes don't exist in the target? I'd be interested in understanding how situations like this arise so we can better solve the issue.

David

Sure thing David. My situation is a continous development environment on a large platform with multiple developers creating SQL articifacts. We have quartlerly releases, and I want to apply the same Database scripts that will be run during our production night deployments during every QA release. These QA releases occur multiple times a day.

The benefit for me is the script on deploy night have spent months of QA, and there are limited errors do to deployment issues.

Obviously the first time I run the script in our QA environment it will be a CREATE, but then the same script will run over and over again untill production deploy, hence the need for the ALTER check.

Does that make sense at a hight level? I have created a deployment tool using C#, Red Gate API tool stack and the SQL Compare API and it runs great. The only custom coding needed was for the ALTER vs CREATE checks. Would love to dump this custom code if you guys can come up with a solution, as I am sure your RegEx experts might do a little better than I did

My end game for this is for deployments to be a few F5 pressess, and minimal hands on from a human. Before my tool we compared our QA to prod environemnts via Snapshots. This worked fine, and left in a much better place than before. But there was still a human involved making judgement calls (For example, was the difference shown part of a hotfix out of cycle or new developemnt, not hard to get this wrong in a large development effort where communication between teams is not always the greatest)

David Atkinson

We're currently working on adding a feature in SQL Compare and SQL Source Control that will let you associate migration scripts with specific transitions between schema versions. This will probably be in conjunction with a version table held on the database which records which version it is. This would mean that you could automate SQL Compare to apply the correct migrations where appropriate. If the target has been hotfixed, we could check this before running the script and warn the user before applying it.

We think that this is a cleaner solution than trying to generate a single sync script that has the intelligence to work on target databases that can look different.

Can I ask whether you are currently using SQL Source Control?

David

einman33

David that sounds very interesting and will probably be an early adopter once it comes out.

Our entire development group is standardized on TortoiseSVN front end with a Subversion back end.

I have SQL Source Control installed and talking to Subversion just have not gone much beyond that.

Hopefully there will be some beta opportunities as I would love to take it out for a test drive.

David Atkinson

Regarding the 'migrations' feature, would you be able to fill in our survey?

http://www.surveymk.com/s/SOC_Migrations

We're looking for as much feedback at the moment because we're actively working on draft designs and deciding what the core requirements should be.

Please give SQL Source Control a go and let us know what you think. It should be trivial to evaluate given that you've already got Subversion (and if it isn't, we'd love to know where you get stuck!).

Kind regards,

David

einman33

David Atkinson wrote:

Regarding the 'migrations' feature, would you be able to fill in our survey?
David

Done, let me plug in with SQL Source Control and post in update.

Koen Mevissen

Hi,

Is there any news on this?

We have been using Sql Source Control for a few weeks, creating our deployment scripts with Sql Compare by comparing revision A with revision B.

We have usually more then 1 code branch (and related DB) alive, and merging from 1 branch to another can only be done by making incremental scripts 'from revision' - 'to revision'.
One of the downsides with this, is we have to somehow figure out which revisions should be scripted in order to create a deploy script.

Sometimes though, we have a situation where for example Sql Compare deploy script tries to create an object which already exists (multiple branches making life hard...)...an IF NOT EXISTS or a similar check would possible solve this issue.

Sorry for this longwinded description, just very curious on any progression or ideas regarding this

David Atkinson

We have options for rerunnable scripts in the latest Beta.

would you be able to let us know if this meets your requirements?

http://www.red-gate.com/MessageBoard/vi ... hp?t=14113

Kind regards,

David