Did Stephen Cook see the significance of showing that SAT is NP-Hard before actually proving it? If I understand correctly, to prove that problem $A$ is NP hard, you need to pick all possible problems $B_{i}$ that are in NP and then prove that they reduce to $A$ by using a polynomial time computable function, that maps instances of each $B_{i}$ to instances of $A$.

Once you find the first NP hard problem, by using reductions you can then find that many other problems are either NP Complete or NP Hard. However I imagine that this depends. If you are unlucky, then maybe all $B_{i}$ problems reduce to $A$, but $A$ reduces nowhere else, so your proof is essentially useless.

My question is about the motivation that Stephen Cook behind showing that the SAT problem is NP hard. Did he see a lot of potential behind this problem? Did he know that if he showed that this problem is NP hard then a lot of other problems could be shown to be NP hard as well?

In short, what is the story behind this proof? Because after studying some basic complexity theory, it really seems like this proof was one of the most significant ones in this area.
First of all, Cook actually showed that the problem of whether a logical expression is a tautology is $\mathbb{NP}$-complete under Cook reductions. The proof however works by replacing them with Karp reductions to show that $SAT$ is $\mathbb{NP}$-complete, in the modern sense of the term.

As for whether Cook understood the significance of a $\mathbb{NP}$-complete problem not being in $\mathbb{P}$, going through the actual paper  shows that he did. However, I believe it wasn't until Karp's list of 21 complete problems that the practical significance of Cook's result began to be understood.