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 Wednesday, October 22, 2008 12:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:48 AM
Points: 2,914, Visits: 1,845
doobya (10/22/2008)
Some very nice techniques and ideas in the article

But 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.




LinkedIn Profile
Newbie on www.simple-talk.com
Post #590048
Posted Wednesday, October 22, 2008 2:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
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?
Post #590119
Posted Wednesday, October 22, 2008 2:53 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, October 31, 2014 6:08 AM
Points: 145, Visits: 218
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.



Post #590125
Posted Wednesday, October 22, 2008 3:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
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.
Post #590140
Posted Wednesday, October 22, 2008 3:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
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.
Post #590146
Posted Wednesday, October 22, 2008 3:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
Just looked up in Subversion: more than one hundred rollback scripts, all generated by SQL Compare of course.
Post #590149
Posted Thursday, October 23, 2008 10:26 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:22 PM
Points: 752, Visits: 918
This was an excellent article and well written. And I cannot overemphasize the point you made about code readability. In fact, I think my team is getting tired of me talking about literate programming.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #590605
Posted Thursday, October 23, 2008 10:51 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: Yesterday @ 1:16 PM
Points: 905, Visits: 1,998
I teach readability by stressing that you're writing code for the person who follows you. It jolts their perception a bit to think that 'this previous project would have been so much easier if it had been better documented, so I'll document my current project better for the person who comes after me.'

Of course, some people, regardless of how well the case is argued, will never improve their documentation and readability. :D


-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Post #590622
Posted Saturday, October 25, 2008 1:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:25 PM
Points: 229, Visits: 719
I see what proposed in the article is an overkill most of time in SQL2K5 EE. For DB side rollback, is there anything better/quicker than the database snapshot? i.e. Do a db snapshot before the deployment, and if anything goes wrong, and you need to research, compare with the snapshot db, or if you want to rollback, rollback with the snapshot db.

Just my two cents... :)

Rgds,
JY



Post #591654
Posted Saturday, October 25, 2008 7:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
jeffrey yao (10/25/2008)
I see what proposed in the article is an overkill most of time in SQL2K5 EE. For DB side rollback, is there anything better/quicker than the database snapshot? i.e. Do a db snapshot before the deployment, and if anything goes wrong, and you need to research, compare with the snapshot db, or if you want to rollback, rollback with the snapshot db.

Just my two cents... :)

Rgds,
JY


That's a good point Jeffrey, have you had any troubles with that method when you rolled back a failed deployement, any hints we need to know to use this method?
Post #591689
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse