I have a mixed love/hate relationship with tests.
I hate writing them. I hate remembering to add them when I'm in the zone, and application code is flowing freely from the tips of my fingers.
But when I do add them, I absolutely love the ability to twist and replace the most core innards of application, and be able to tell that at least the sanity check of the code passes.
I love them even more when they prevent me for repeating some mistake/bug – i.e. when there is a bug, and I have tests (which clearly failed, as they didn't catch the bug), I add test for this specific bug, so I know that in future it will not happen again.
For a long time I've been fan of stored procedures (or functions) – of course not for everything, but where it makes sense.