This can really save your hide when you hit a reproducible issue. Proper error handling is part of proper debugging, too.
I generally detest PowerShell, but I love that some commands have a
-WhatIfparameter to test what would happen. For normal SQL Server queries, you don’t get that.
But if you’re writing dynamic SQL that may execute database commands (backup, checkdb, settings changes, etc.), you should also include a parameter that prevents dynamic SQL execution so you can debug without worrying about wreaking havoc.
Read the whole thing. I might handle the specific mechanisms of debugging slightly differently, but Erik’s packed this post full of good advice.