One of the advantages of test driven development is that it gives you a nice set of tests which you can continue to use, and extend, for refactoring purposes. This will help to minimize bugs that are produced, and even help to isolate them in your systems (writing a test that can reproduce a known bug is an excellent way to find it in the code). Tests also serve as a great form of documentation. What better way to see how to use a system than to see it in action? Of course, that doesn't mean you shouldn't provide documentation with your systems, that just means that when you need to make changes, or utilize a system, then having the tests available for your viewing pleasure is handy.
I have never been let down by unit tests nor TDD. Way back in the day, before I began seriously using TDD, I found that I spent a great deal of time in the debugger. Get a bug report, locate it approximately, then step through the code and set breakpoints as needed (conditional and other) to isolate the bug to a section of the code. Then I would typically have to analyze the behavior of the code and determine where the fault lay. This was a time consuming process, and during that time I wasn't getting any development done on new features. Using TDD however, I find that I rarely, if ever, have to use the debugger to find a bug. Typically my tests will immediately indicate where the fault lies and I can fix it in short order. I've got a mantra that I use frequently when talking to myself (you do it too, don't lie to me) "Write lots of tests, and run them frequently." It's a great mantra to be sure, and one I would hope some of you would adopt.
There are issues however. Tests are not comprehensive. It would be improbable to test every possible combination of inputs to a program, note that it's not impossible, just improbable. The possible numbers of combinations are enormous, but finite. So this means that tests won't identify all bugs. But they check the most common cases, which is all we really care about. However, sometimes we miss common cases when writing tests, either through ignorance or just because it was never obvious. These are more serious cases in which serious bugs can get through a system. Hopefully, however, these will be caught during the product testing stages of development and allow you to correct them at that point in time. Test driven development and unit testing are not a panacea. They are merely tools that a developer can use to help speed up the process of developing and debugging software.
So what testing software do I use? Lots of them. For .Net development I tend to use NUnit, along with TestDriven.net, for SQL there is TSQLUnit, and finally, for ASP.Net there is NUnitAsp.