MathOverflow will be down for maintenance for approximately 3 hours, starting Monday evening (06/24/2013) at approximately 9:00 PM Eastern time (UTC-4).

Further edit: For categories of finitely generated modules here is something else, although it is more in the direction of the title of your question than what is in the actual body of the question. Suppose we let $R$ be a commutative noetherian regular ring with unit and let $R$-mod be the category of finitely generated $R$-modules. Then we can get a description of $R$-mod$^{op}$ using duality in the derived category.
Since $R$ is regular every object of $D^b(R) \colon= D^b(R-mod)$ is compact in the full derived category. The point is that$$RHom(-,R)\colon D^b(R)^{op} \to D^b(R)$$is an equivalence (usually this is only true for perfect complexes, but here by assumption everything is perfect). So one can look at the image of the standard t-structure (which basically just "filters" complexes by cohomology) under this duality. The heart of the standard t-structure is $R$-mod sitting inside $D^b(R)$ so taking duals gives an equivalence of $R$-mod$^{op}$ with the heart of the t-structure obtained by applying $RHom(-,R)$ to the standard t-structure.
In the case of $R =k$ a field then this just pointwise dualizes complexes so we see that it restricts to the equivalence $k$-mod$^{op}\to k$-mod given by the usual duality on finite dimensional vector spaces.
As another example consider $\mathbb{Z}$-mod sitting inside of $D^b(\mathbb{Z})$ as the heart of the standard t-structure given by the pair of subcategories of $D^b(\mathbb{Z})$$\tau^{\leq 0} = \{X \; \vert \; H^i(X) = 0 \; \text{for} \; i>0\}$$$$\tau^{\geq 1} = \{X \; \vert \; H^i(X) = 0 \; \text{for} \; i\leq 0\}$$It is pretty easy to check that$RHom(\Sigma^i \mathbb{Z}^n, \mathbb{Z}) \cong \Sigma^{-i} \mathbb{Z}^n$and$RHom(\Sigma^i \mathbb{Z}/p^n\mathbb{Z}, \mathbb{Z}) \cong \Sigma^{-i-1}\mathbb{Z}/p^n\mathbb{Z}$so that this t-structure gets sent to$$\sigma^{\leq 0} = \{X \; \vert \; H^i(X) = 0 \; \text{for} \; i> 0, \; H^{0}(X) \; \text{torsion}\}$$$$\sigma^{\geq 1} = \{X \; \vert \; H^i(X) = 0 \; \text{for} \; i< 0, H^0(X) \; \text{torsion free}\}$$using the fact that objects of$D^b(\mathbb{Z})$are isomorphic to the sums of their cohomology groups appropriately shifted. So taking the heart we see that$$\mathbb{Z}-mod^{op} \cong \sigma^{\leq 0} \cap \Sigma\sigma^{\geq 1} = \{X \; \vert \; H^{-1}(X) \; \text{torsion free}, H^0(X) \; \text{torsion}, H^i(X) = 0 \; \text{otherwise}\}$$with the abelian category structure on the right coming from viewing it as a full subcategory of$D^b(\mathbb{Z})$with short exact sequences coming from triangles. It is the tilt of$\mathbb{Z}$-mod by the standard torsion theory which expresses every finitely generated abelian group as a torsion and torsion free part. 1 One can prove that for any non-zero ring$R$the category$R$-Mod$^{op}$is not a category of modules. Indeed any category of modules is Grothendieck abelian i.e., has exact filtered colimits and a generator. So for$R$-Mod$^{op}$to be a module category$R\$-Mod would also need exact (co)filtered limits and a cogenerator. It turns out that any such category consists of just a single object.