What does equivalence of categories really tell us?
I think this is a great question, and I don't pretend to have a full answer for it, but here are some of my thoughts:
Exactly how happy should I be when I prove that two seemingly different categories are equivalent?
This depends on exactly how "seemingly different" they are - or why you're trying to prove this in the first place. I'll illustrate this with respect to your examples:
the equivalence between classical varieties and finite-type integral schemes
You shouldn't be surprised that these the same thing: the latter was practically designed to be a new way of encoding the former! But from the perspective of a researcher who only knows the former, and is trying to invent a new formalism to encode it, you should certainly be happy about it, because it tells you that the formalism is in some sense "correct" - it doesn't gain or lose too much information categorically.
commutative $C^∗$-algebras and the opposite category of compact Hausdorff topological spaces ... To which $C^∗$-algebras do manifolds correspond?
This is really a question about manifolds inside the category of compact Hausdorff topological spaces, and not about the equivalence of categories. Is the property of being a manifold a categorical property? That is, suppose I hand you the abstract category of compact Hausdorff topological spaces, and I don't allow you to actually use any topological properties (e.g. I just give you a bunch of vertices and arrows with composition data, and I don't tell you which spaces the vertices represent or which morphisms the arrows represent). Can you pick out the manifolds from this?
A stupid example of my own: let C be the category of finite-dimensional vector spaces. It's easy to pick out the vector spaces of dimension 3, as follows. The spaces of dimension 0 will be initial objects, so you know which those are; then, inductively, the spaces of dimension n+1 will be the objects x that aren't spaces of dimension < n+1, and such that the only morphisms into x are those coming from spaces of dimension < n+1 or those coming from isomorphic objects.
Now, maybe you can pick out the manifolds categorically. I don't actually know. But my suspicion is, if you can, it's not in any nice way, and so when you translate it into the language of $C^*$-algebras, you probably don't get anything usable.
Here's an example closer to my own heart: Lazard's famous equivalence of categories between certain nice $\mathbb{Z}_p$-Lie algebras and certain nice pro-$p$ groups. The devil's in the detail: I've hidden, behind both instances of the word "certain", a bunch of technical conditions that are more or less designed to make this true. But it's still remarkable that it's true at all.
Moreover, the real-life usefulness of this fact is guaranteed by the fact that all closed subgroups of $GL_n(\mathbb{Z}_p)$ do actually contain one of these certain nice pro-$p$ groups as a closed subgroup of finite index. In other words, no matter how horrible your closed subgroup of $GL_n$ is, viewed from the right angle it's more or less just (a finite amount of complexity away from) a Lie algebra. Whereas you previously only had the tools of group theory to apply to this object, you now also have the tools of Lie theory. That's definitely a win: even just knowing that finite-rank $\mathbb{Z}_p$-modules have a basis tells you a lot about the group and its completed group algebra, e.g. a PBW-type theorem.