InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 11:01:02 GMT20http://www.sqlservercentral.com/Forums/Topic589587-60-1.aspx[quote][b]mike (10/22/2008)[/b][hr]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[/quote]No, I think it's right as posted. If you make your change, it drops the FK constraints on the tables you are dropping. You don't care about those - they will go away when you drop the tables. As written, it drops FK constraints on OTHER tables, which you may or may not be dropping, that point back to the tables you are dropping.Try running it with Vendor as the table to drop. You'll see it dropping the FK constraints on ProductVendor, VendorAccess and VendorContact that all have a VendorID column that points back into the Vendor table. You have to drop those constraints (on the tables you are [b]not [/b]dropping) before you can drop the Vendor table. (I had to stare at it a bit to see what was happening, too.)Tue, 28 Oct 2008 08:26:15 GMTCAGreensfelderhttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxI have used snapshot extensively in my test environment but not in the prod deployment yet. The reason is not the technology itself, but because we have never failed our deployment yet. :-)The only thing I'd like to emphasize here is: stop all sql server agent services on all servers before doing any snapshots (esp. helpful when you have replications)Sat, 25 Oct 2008 23:20:43 GMTjeffrey yaohttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspx[quote][b]jeffrey yao (10/25/2008)[/b][hr]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[/quote]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?Sat, 25 Oct 2008 07:38:05 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxI 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,JYSat, 25 Oct 2008 01:06:12 GMTjeffrey yaohttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxI 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. :DThu, 23 Oct 2008 10:51:16 GMTWayne Westhttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxThis 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.Thu, 23 Oct 2008 10:26:04 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxJust looked up in Subversion: more than one hundred rollback scripts, all generated by SQL Compare of course.Wed, 22 Oct 2008 15:18:28 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspx[quote][b]hurcane (10/22/2008)[/b][hr][quote][b]Alexander Kuznetsov (10/22/2008)[/b][hr]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?[/quote]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.[/quote]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.Wed, 22 Oct 2008 15:15:27 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxStupid 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.Wed, 22 Oct 2008 15:09:09 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspx[quote][b]Alexander Kuznetsov (10/22/2008)[/b][hr]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?[/quote]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.Wed, 22 Oct 2008 14:53:40 GMThurcanehttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxHi,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?Wed, 22 Oct 2008 14:48:49 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspx[quote][b]doobya (10/22/2008)[/b][hr]Some very nice techniques and ideas in the articleBut I have never rolled back a change to a production database in 10 years.[/quote]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.Wed, 22 Oct 2008 12:45:30 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxI 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.Wed, 22 Oct 2008 10:43:27 GMTkatedgrthttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxInteresting 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.Wed, 22 Oct 2008 09:48:18 GMTWayne Westhttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxthis if fine for views and sp's butif you change an object drop a field or drop a table change foriegn keys this isnt going to be friendlyWed, 22 Oct 2008 09:16:51 GMTdanschlhttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxHow 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?Wed, 22 Oct 2008 06:59:01 GMThurcanehttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxNice 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 thisWHERE 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.Wed, 22 Oct 2008 06:49:40 GMTdevelopmentalmadnesshttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxSome very nice techniques and ideas in the articleBut 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.Wed, 22 Oct 2008 06:36:57 GMTDataDoghttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxjust buy a tool... :hehe:Wed, 22 Oct 2008 03:08:04 GMTRichBhttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxIn 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?MikeWed, 22 Oct 2008 01:37:05 GMTmike-658424http://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxVery informative article. Well written...Wed, 22 Oct 2008 00:46:28 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic589587-60-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Deployment+Process/64422/">Fast Project Rollbacks</A>[/B]Tue, 21 Oct 2008 23:31:58 GMTDavid.Poole