SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fast Project Rollbacks


Fast Project Rollbacks

Author
Message
Dave Poole
Dave Poole
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42332 Visits: 3857
Comments posted to this topic are about the item Fast Project Rollbacks

LinkedIn Profile
www.simple-talk.com
Anipaul
Anipaul
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16561 Visits: 1407
Very informative article. Well written...



mike-658424
mike-658424
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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
RichB
RichB
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6866 Visits: 1085
just buy a tool... Hehe



DataDog
DataDog
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2021 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.
developmentalmadness
developmentalmadness
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 132
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
hurcane
hurcane
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 Visits: 255
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?



danschl
danschl
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4046 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



Wayne West
Wayne West
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14100 Visits: 3710
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.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
katedgrt
katedgrt
Right there with Babe
Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)

Group: General Forum Members
Points: 751 Visits: 990
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search