Paul Randal (5/6/2013)
I hate to be negative, but it's extremely unfortunate that this script truncates the log, breaking the log backup chain, and that isn't explained anywhere in the script or the comments. Even if it was explained, this resets all your disaster recovery options afterwards to starting with the full backup it advises you to take at the end. Previous backups cannot be used to recover past the log truncation.
Be extremely wary about using this script in production. There's a reason we (as I was on the SQL team at the time) removed the TRUNCATE_ONLY/NO_LOG options to BACKUP LOG in SQL Server 2008.
thank you for explaining. I had a suspicion that something was scary about this script.
There are comments, but there are no explanations. I kept looking for some information on how/why this series of steps was affecting the proposed change. I appreciate that nobody should be running code they find online without understanding exactly what it does... but it would be nice to be told that inline with the commands rather than creating a research project for me.
An overview of why this solution is 'better' than the tools provided by Microsoft would be an interesting read too. My guess is that you don't use this script on trivial databases because it isn't necessary. I read Paul's suggestion that you don't use this script on appreciably large/important databases because it is dangerous. Please explain the circumstances where this script _should_ be used (caveats, risks, etc.)
Also, if you are so inclined - share with us any "gone wrong" experiences while writing/debugging this script if you thrashed a DB before you got it working