October 21, 2008 at 11:31 pm
Comments posted to this topic are about the item Fast Project Rollbacks
October 22, 2008 at 12:46 am
Very informative article. Well written...
October 22, 2008 at 1:37 am
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
October 22, 2008 at 3:08 am
just buy a tool... :hehe:
October 22, 2008 at 6:36 am
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.
October 22, 2008 at 6:49 am
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.
October 22, 2008 at 6:59 am
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?
October 22, 2008 at 9:16 am
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
October 22, 2008 at 9:48 am
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.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 22, 2008 at 10:43 am
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 :w00t:
If you don't have time to do it right the first time, where will you find time to do it again?
October 22, 2008 at 12:45 pm
doobya (10/22/2008)
Some very nice techniques and ideas in the articleBut I have never rolled back a change to a production database in 10 years.
I rarely have to roll back a live deployment but deployments into a QA environment are another matter. And before anyone jumps up and down QA in this case is the environment where we throw testers and bloody minded users at systems to see if they can break the code.
As we move towards more agile practices the ability to rollback/deploy on the fly are important.
As far as tools are concerned you can use Red-Gate Compare and generate a snapshot of the schema. Cheap though the tool is not all companies see the benefit of buying a £400 tool against £x,000 in DBA time.
In terms of deleting columns from existing tables I take either the backup table approach or bcp the file and table create script out to a tape before I do it. It depends on how big the table is.
To be honest we are much more likely to add/change columns than delete them. When we do delete them we are pretty damn sure they will never be used again.
October 22, 2008 at 2:48 pm
Hi,
I can use something like SQL Compare which has been around for a while, has been thoroughly tested out by thousands of users, and probably does all (or almost all) this. Why should I spend my time on doing something which has been done before and works very well? Unless I am missing something, this sounds like reinventing the wheel. What am I missing?
October 22, 2008 at 2:53 pm
Alexander Kuznetsov (10/22/2008)
Hi,I can use something like SQL Compare which has been around for a while, has been thoroughly tested out by thousands of users, and probably does all (or almost all) this. Why should I spend my time on doing something which has been done before and works very well? Unless I am missing something, this sounds like reinventing the wheel. What am I missing?
SQL Compare does not generate "rollback" scripts. The article was not about deployment scripts. It was about writing rollback scripts to undo the changes that SQL Compare would generate.
October 22, 2008 at 3:09 pm
Stupid question here... but what happens if you reverse the order of the servers just after creating the migration scripts?
Doesn't that help you create the rollback scripts.
I'm sure it won't put back lost or altered data, but if that's the only thing left to worry about, it seems like a great start to me.
October 22, 2008 at 3:15 pm
hurcane (10/22/2008)
Alexander Kuznetsov (10/22/2008)
Hi,I can use something like SQL Compare which has been around for a while, has been thoroughly tested out by thousands of users, and probably does all (or almost all) this. Why should I spend my time on doing something which has been done before and works very well? Unless I am missing something, this sounds like reinventing the wheel. What am I missing?
SQL Compare does not generate "rollback" scripts. The article was not about deployment scripts. It was about writing rollback scripts to undo the changes that SQL Compare would generate.
SQL Compare absolutely does generate rollback scripts. I do just that every time I deploy: open up SQL Compare, generate deploy/rollback scripts, test them both out, check them in in Subversion etc.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply