Red Gate forums :: View topic - SSMS-Tasks-Generate Scripts - add Drop if exists to script
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare Previous Versions
SQL Compare Previous Versions forum

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

Search in SQL Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
dstarkey



Joined: 26 Jan 2011
Posts: 1

PostPosted: Wed Jan 26, 2011 4:53 pm    Post subject: SSMS-Tasks-Generate Scripts - add Drop if exists to script Reply with quote

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??
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Thu Jan 27, 2011 5:25 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Thu Jan 27, 2011 5:44 pm    Post subject: Re: SSMS-Tasks-Generate Scripts - add Drop if exists to script Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
einman33



Joined: 31 Jan 2011
Posts: 5

PostPosted: Mon Jan 31, 2011 9:28 pm    Post subject: Reply with quote

What about for table objects:

IF EXSITS....ALTER TABLE else CREATE TABLE?
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Tue Feb 01, 2011 12:20 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
mattkarp



Joined: 01 Nov 2010
Posts: 6

PostPosted: Wed Feb 02, 2011 2:56 pm    Post subject: Deloyment into a black box Reply with quote

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.
Back to top
View user's profile Send private message
einman33



Joined: 31 Jan 2011
Posts: 5

PostPosted: Thu Feb 03, 2011 10:28 pm    Post subject: Re: Reply with quote

[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)
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Fri Feb 04, 2011 11:51 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
einman33



Joined: 31 Jan 2011
Posts: 5

PostPosted: Sun Feb 06, 2011 2:08 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Sat Feb 12, 2011 5:40 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
einman33



Joined: 31 Jan 2011
Posts: 5

PostPosted: Mon Feb 14, 2011 5:33 pm    Post subject: Re: Reply with quote

[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.
Back to top
View user's profile Send private message
Koen Mevissen



Joined: 26 Jan 2011
Posts: 4

PostPosted: Wed Jun 15, 2011 3:14 pm    Post subject: Reply with quote

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 Smile
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Fri Oct 28, 2011 12:06 pm    Post subject: Reply with quote

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/viewtopic.php?t=14113

Kind regards,

David
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group