First, no, SQL injection isn't the primary way people lose password databases. It's a significant component of those incidents, but just one of them.
Second, even with SQL injection, keeping a secret key somewhere on the disk is unlikely to be helpful, because SQL injection attacks usually cough up remote code execution, and even more frequently provide direct read access to the filesystem; on many hugely popular frameworks, "arbitrary file read" is itself remote code execution.
Hence why I mentioned separate servers. Even if you did screw up the database permissions and allow an injection attack to have more than just read permissions, they'd still only have access to the DB server filesystem.
Unless there is some extra network vulnerability that lets them then hop to the webserver to read the secret pepper straight out of memory, I can't see why it isn't worthwhile. It may only provide a modicum of extra security, but it also is trivial to implement. Unless there is a cryptographic downside to adding the pepper (possible - outside my area of expertise), I can't see why not?
No I mean I already have two physically separate servers; one with the web service (Apache/Nginx + Rails/PHP/whatever), one with the database. The webserver handles the hashing of passwords as they come in, then sends it to the database for storage.
You're starting make me feel odd for not having the database on the same server as the web service now. I feel we've probably got some crossed wires here...
Second, even with SQL injection, keeping a secret key somewhere on the disk is unlikely to be helpful, because SQL injection attacks usually cough up remote code execution, and even more frequently provide direct read access to the filesystem; on many hugely popular frameworks, "arbitrary file read" is itself remote code execution.