Reading the subsequent comments, I think the most common response to my arguments was "yes, but so what?" and the second most common was "nuh uh!" followed by a set of further utopian best-practices (such as "isolate the UI from the business logic").
There's a fundamental misunderstanding of the argument here. I said from the get-go that unit tests can be useful and I don't have anything against them. My problem is with those who:
a) Believe that they're a legitimate substitute for actual design and documentation
b) Honestly think that they can test for every conceivable bug
c) Recommend - nae, INSIST on it as a "best practice".
Each of these lines of reasoning is not only sloppy, but dangerous.
If your program is anything but a throwaway or one-off, then there's no excuse for hacking it out without a detailed requirements document and design/functional spec. I've already spotted a few ninja-style programmers in the comments who "just wanna code" and use "but I have unit tests!" as an excuse to prove that they've done it correctly even though there's no supporting documentation. This attitude will burn you in the end. Some people adore unit tests precisely because they are simply code, and believe they've discovered a way to avoid the mind-numbing work of documenting things. This is patently wrong. Unit tests aren't understood by your customers, and they won't help a maintenance programmer much either (I know from experience).
Unit tests may prove that a particular class or method works, but contrary to popular belief, it does not -always- prove that it works correctly. As Jonathan Allen pointed out, you are testing the part of your program that is not only least likely to fail, but also the easiest to debug later on and the least expensive to fix. The real problems, the most difficult ones to fix, are the design flaws. Interdependencies between routines and classes. Semantic mistakes. Sequential coupling. Heisenbugs/schroedinbugs. Erratic behaviour under low-memory conditions. Strange errors getting propagated from library (i.e. DB or networking) code due to things like poor networking conditions. Unit tests test help with precisely 0% of these. If design problems account for 80% of the debugging (I'd say that's a conservative estimate) then even if unit tests halved the time spent on code problems, it's still only saved you 10%, and that may very well be offset by the time spent creating and maintaining the unit tests.
Unit testing is just one tool that can be used in a full QA program. If the people who preach Unit Tests got half as excited about other types of QA (i.e. exploratory testing and code reviews) then I might be more inclined to listen to them, but instead I get the very strong feeling that they consider it an adequate substitute for a full QA program, which it isn't. There are no best practices. Learn how to do your job, which means knowing precisely how much or how little testing your project requires, and what the best test methods really are. Don't just assume that unit tests will magically solve all your problems.
I have a real problem with Unit Testing gospel because I've seen the above three points in practice. In fact, these assumptions are implicit in many development and consulting shops who claim to use "Agile" methodology. I note that true Agile development can be extremely effective but I am referring here to the vast hordes who use Agile/XP as a buzzword. I've actually seen people state that they don't need documentation and that Unit Tests are just fine for a complete QA program. I've seen the result of this thinking in practice. It's always a disaster. The resulting product never works correctly. The evidence usually seems to indicate that the people who made it don't even completely understand what it's supposed to do - they just dove into the code without thinking and did what THEY wanted to do without heed for actual requirements. Ask for documentation and months may go by before you receive anything. Serious errors are fixed slowly. Enhancements, if implemented at all, can take ages.
Bottom line: Unit Tests are often good. Unit Testing Evangelism is always bad.