What are the challenges you face when working across database platforms? Take the survey
Options

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

dstarkeydstarkey Posts: 2
edited October 28, 2011 7:06AM in SQL Compare Previous Versions
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??

Comments

  • Options
    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.
    Systems Software Engineer

    Redgate Software

  • Options
    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
    David Atkinson
    Product Manager
    Redgate Software
  • Options
    What about for table objects:

    IF EXSITS....ALTER TABLE else CREATE TABLE?
  • Options
    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
    David Atkinson
    Product Manager
    Redgate Software
  • Options
    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.
  • Options
    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)
  • Options
    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
    David Atkinson
    Product Manager
    Redgate Software
  • Options
    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.
  • Options
    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
    David Atkinson
    Product Manager
    Redgate Software
  • Options
    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.
  • Options
    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 :)
  • Options
    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
    David Atkinson
    Product Manager
    Redgate Software
Sign In or Register to comment.