Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Fast Project Rollbacks Expand / Collapse
Author
Message
Posted Tuesday, October 21, 2008 11:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:07 AM
Points: 2,901, Visits: 1,805
Comments posted to this topic are about the item Fast Project Rollbacks

LinkedIn Profile
Newbie on www.simple-talk.com
Post #589587
Posted Wednesday, October 22, 2008 12:46 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 5,353, Visits: 1,389
Very informative article. Well written...


Post #589619
Posted Wednesday, October 22, 2008 1:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, January 18, 2014 3:41 AM
Points: 107, Visits: 203
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
Posted Wednesday, October 22, 2008 3:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 1,070, Visits: 901
just buy a tool...


Post #589670
Posted Wednesday, October 22, 2008 6:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:02 AM
Points: 147, Visits: 426
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
Posted Wednesday, October 22, 2008 6:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 9, 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
Posted Wednesday, October 22, 2008 6:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:53 AM
Points: 144, Visits: 214
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
Posted Wednesday, October 22, 2008 9:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 25, 2013 7:43 AM
Points: 1,384, Visits: 644
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
Posted Wednesday, October 22, 2008 9:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 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
Posted Wednesday, October 22, 2008 10:43 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 12:33 PM
Points: 225, Visits: 988
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.


Kate The Great
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse