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

Forum for users of SQL Compare schema synchronization utility

Moderators: JonathanWatts, Chris Auckland, David Atkinson

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

Postby dstarkey » Wed Jan 26, 2011 4:53 pm

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??
dstarkey
 
Posts: 1
Joined: Wed Jan 26, 2011 4:49 pm

Postby james.billings » Thu Jan 27, 2011 5:25 pm

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

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

Postby David Atkinson » Thu Jan 27, 2011 5:44 pm

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
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby einman33 » Mon Jan 31, 2011 9:28 pm

What about for table objects:

IF EXSITS....ALTER TABLE else CREATE TABLE?
einman33
 
Posts: 5
Joined: Mon Jan 31, 2011 7:27 pm

Postby David Atkinson » Tue Feb 01, 2011 12:20 am

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
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Deloyment into a black box

Postby mattkarp » Wed Feb 02, 2011 2:56 pm

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.
mattkarp
 
Posts: 6
Joined: Mon Nov 01, 2010 7:03 pm

Re:

Postby einman33 » Thu Feb 03, 2011 10:28 pm

[quote="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[/quote]

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)
einman33
 
Posts: 5
Joined: Mon Jan 31, 2011 7:27 pm

Postby David Atkinson » Fri Feb 04, 2011 11:51 am

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
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby einman33 » Sun Feb 06, 2011 2:08 am

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.
einman33
 
Posts: 5
Joined: Mon Jan 31, 2011 7:27 pm

Postby David Atkinson » Sat Feb 12, 2011 5:40 pm

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
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Re:

Postby einman33 » Mon Feb 14, 2011 5:33 pm

[quote="David Atkinson"]Regarding the 'migrations' feature, would you be able to fill in our survey?
David[/quote]

Done, let me plug in with SQL Source Control and post in update.
einman33
 
Posts: 5
Joined: Mon Jan 31, 2011 7:27 pm

Postby Koen Mevissen » Wed Jun 15, 2011 3:14 pm

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 :)
Koen Mevissen
 
Posts: 4
Joined: Wed Jan 26, 2011 8:55 pm

Postby David Atkinson » Fri Oct 28, 2011 12:06 pm

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
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase


Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests