Fast Project Rollbacks

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Poole » Fast Project Rollbacks

22 posts, Page 1 of 3

1 2 3 »»»

Fast Project Rollbacks

Rate Topic

Display Mode

Topic Options

Author

Message

David.Poole

Posted Tuesday, October 21, 2008 11:31 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:16 PM
Points: 2,749, Visits: 1,405

Comments posted to this topic are about the item Fast Project Rollbacks

LinkedIn Profile

Post #589587

Anipaul

Posted Wednesday, October 22, 2008 12:46 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:50 AM
Points: 4,785, Visits: 1,334

Very informative article. Well written...

Post #589619

mike-658424

Posted Wednesday, October 22, 2008 1:37 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 7:21 AM
Points: 99, Visits: 201

In the code that generates the DROP CONSTRAINTS for the foreign keys, I can't get this to work unless I change rkeyid in the WHERE clause into fkeyid, which kinda makes sense since that is used in the OBJECT_NAME function.

Is this a mistake?

Mike

Post #589633

RichB

Posted Wednesday, October 22, 2008 3:08 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:59 AM
Points: 1,026, Visits: 750

just buy a tool... Hehe

Post #589670

DataDog

Posted Wednesday, October 22, 2008 6:36 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 7:07 AM
Points: 146, Visits: 421

Some very nice techniques and ideas in the article

But I have never rolled back a change to a production database in 10 years.

Your energy would be much better spent ensuring that deployment does not *need* to be rolled back.

And being ready to fix any minor issues that crop up ...

If you are not 100% sure deployment will succeed - do not deploy.

Post #589755

developmentalmadness

Posted Wednesday, October 22, 2008 6:49 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, November 09, 2012 5:11 AM
Points: 203, Visits: 131

Nice article! A suggestion to improve readability. If you have a table valued function which can split a delimited string then you could do the following at the top of the script:

DECLARE @Tables VARCHAR(MAX), @Views VARCHAR(MAX), @Procedures VARCHAR(MAX)

SELECT
@Tables = 'Table1,Table2,Table3',
@Views = 'View1,View2,View3',
@Procedures = 'Procedure1,Procedure2,Procedure3'

Then your WHERE clause would just look something like this

WHERE name IN (SELECT Value FROM dbo.SplitString(@Tables))

This would be more readable (you only have to look one place for which objects will be affected). If you don't like the TVF suggestion you could just declare table variables and insert the names in and your WHERE clause would just reference the table variable instead.

blog: http://developmentalmadness.blogspot.com

Post #589761

hurcane

Posted Wednesday, October 22, 2008 6:59 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 06, 2013 2:30 PM
Points: 143, Visits: 191

How do you handle objects (especially tables or columns) that were removed as part of a deployment? How does the rollback script get them back? Do you never actually delete any data?

Post #589763

danschl

Posted Wednesday, October 22, 2008 9:16 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:37 AM
Points: 1,382, Visits: 641

this if fine for views and sp's but
if you change an object

drop a field or drop a table
change foriegn keys this isnt going to be friendly

Post #589875

Wayne West

Posted Wednesday, October 22, 2008 9:48 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 09, 2012 10:26 AM
Points: 891, Visits: 1,958

Interesting article, David, thanks for writing it, I'm going to need to devote some time to studying and playing with the code. I've never had to script out updates like this, but I can definitely see it as a good practice. I like the way you get things into the log, I can see using that in other processes that I currently have running.

Post #589894

katedgrt

Posted Wednesday, October 22, 2008 10:43 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 11:20 AM
Points: 225, Visits: 984

I really enjoyed the article and will refer back to the code snips later for sure. I'm awfully surprised by the people who 1) don't think a rollback plan is necessary (it must be nice to be perfect!) and 2) would rather buy their way out of the necessity to code. Unfortunately, I've never met a tool that would address a full migration rollback. I'm interested to know what tool we are referring to.

As for the what abouts... some of these will simply require different coding techniques, others, such as a dropped column, may require partial restores, but I agree with the author that a full restore of a production environment to undo a deployment should never be your entire plan.

Cool

Kate The Great w00t

If you don't have time to do it right the first time, where will you find time to do it again?

Post #589950

« Prev Topic | Next Topic »

22 posts, Page 1 of 3

1 2 3 »»»

Permissions