Sheaf cohomology is the right derived functor of the global section functor, regarded as a left-exact functor from abelian sheaves on a topological space (more generally, on a site) to the category of abelian groups. In fact, one can regard this functor as $\mathcal{F} \mapsto \hom_{\mathrm{sheaves}}(\ast, \mathcal{F})$ where $\ast$ is the constant sheaf with one element (the terminal object in the category of all -- not necessarily abelian -- sheaves, so sheaf cohomology can be recovered from the full category of sheaves, or the "topos:" it is a fairly natural functor.
de Rham cohomology can be made to work for arbitrary algebraic varieties: there is something called algebraic de Rham cohomology (which is the hyper-sheaf cohomology of the analog of the usual de Rham complex with algebraic coefficients) and it is a theorem of Grothendieck that this gives the usual singular cohomology over the complex numbers. Incidentally, sheaf cohomology provides a very simple proof that de Rham cohomology agrees with ordinary cohomology (at least when you agree that ordinary cohomology is cohomology of the constant sheaf, here $\mathbb{R}$) because the de Rham resolution is a soft resolution of the constant sheaf $\mathbb{R}$, and you can thus use it to compute cohomology.
Sheaf cohomology is quite natural if you want to consider questions like the following: say you have a surjection of vector bundles $M_1 \to M_2$: then when does a global section of $M_2$ lift to one of $M_1$? The obstruction is in $H^1$ of the kernel. So, for instance, this means that on an affine, there is no obstruction. On a projective scheme, there is no obstruction after you make a large Serre twist (because it is a theorem that twisting a lot gets rid of cohomology). Sheaf cohomology arises when you want to show that something that can be done locally (i.e., lifting a section under a surjection of sheaves) can be done globally.
$H^1$ is also particularly useful because it classifies torsors over a group: for instance, $H^1$ of a Lie group on a manifold $G$ classifies principal $G$-bundles, $H^1$ of $GL_n$ classifies principal $GL_n$-bundles (which are the same thing as $n$-dimensional vector bundles), etc.
Also, sheaf cohomology does show up in algebraic topology. In fact, the singular cohomology of a space with coefficients in a fixed group is just sheaf cohomology with coefficients in the appropriate constant sheaf (for nice spaces, anyway, say locally contractible ones; this includes the CW complexes algebraic topologists tend to care about). For instance, Poincare duality in algebraic topology can be phrased in terms of sheaves. Recall that this gives an isomorphism
$H^p(X; k) \simeq H^{n-p}(X; k)$ for a field $k$ and an oriented $n$-dimensional manifold $X$, say compact. This does not look very sheaf-ish, but in fact, since these cohomologies are really $\mathrm{Ext}$ groups of sheaves (sheaf cohomology is a special case of $\mathrm{Ext}$), so we get a perfect pairing
$$ \mathrm{Ext}^p(k, k) \times \mathrm{Ext}^{n-p}(k, k)\to \mathrm{Ext}^n(k,k)$$
where the $\mathrm{Ext}$ groups are in the category of $k$-sheaves. This can be generalized to singular spaces, but to do so requires sheaf cohomology (and derived categories): the reason, I think, that for manifolds those ideas don't enter is that the "dualizing complex" that arises in this theory is very simple for a manifold. You might find useful these notes on Verdier duality, which explains the connection (and which mostly follow the book by Iversen).
If by applications you allow applications to other mathematical field, then here two examples.
The first one is Ax's theorem. It states that a polynomial function $\mathbb C^n \to \mathbb C^n$ is injective if and only if it is bijective. Basically, the proof is as follow : it is clearly true for (polynomial) functions $\mathbb F^n \to \mathbb F^n$ for any finite field $\mathbb F$ ; the Nullstellenstatz then implies that is also true for polynomial functions $\overline{\mathbb F}^n \to \overline{\mathbb F}^n$ with $\overline{\mathbb F}$ the algebraic closure of a finite field ; then comes the model-theoretic argument with Los's theorem which shows that it is again true in the ultraproduct of all algebraic closure of finite field. Then it can be seen that this ultraproduct is an algebraically closed field with characteristic $0$ and infinite transcendence degree over $\mathbb Q$, that is $\mathbb C$.
The second example is the Nullstellensatz itself. It is an immediat corollary of the model-completeness of the theory of algebraically closed fields.
Best Answer
You have the right impression: most applications of model theory to algebraic geometry are essentially "classical", in the sense that they are about varieties as definable sets over algebraically closed fields, not (explicitly) about sheaves and schemes. The model theoretic approach is actually very similar to the view of the foundations of algebraic geometry promoted by André Weil in the 40s (the monster model is essentially Weil's universal domain) which was largely superseded by Grothendieck's approach not long after.
One obstruction to the use of sheaves in model-theoretic algebraic geometry is that it is the constructible topology, not the Zariski topology, which is most natural in model theory. Here algebraic sets are clopen, not just closed - this corresponds to the fact that first-order languages are closed under complement (negation) and projection (quantification). Of course, the constructible topology is totally disconnected, which removes much of the geometric content captured by sheaves.
On the other hand, model theory has proven to be very useful in fields adjacent to algebraic geometry, where a good theory of schemes is not available or is much more complicated than in the classical case. I'm thinking of semialgebraic geometry (and o-minimal generalizations), differential algebra, difference algebra, Berkovich spaces, etc.
That's not to say that schemes and sheaves are nowhere to be found in model theory - I'll leave it to someone else to give some references to some places where they appear. For my part, I'll link you to Angus Macintyre's paper Model Theory: Geometrical and Set-Theoretic Aspects and Prospects from 2003. Here Macintyre surveys the history of model theory and suggests (in a somewhat vague way) a future in which model theory has more in common with Grothendieck-style algebraic geometry. I think this paper has been fairly influential, but the revolution hasn't arrived yet.