Functional programming has to contend with "the real world", which is stateful. FP isn't about eliminating computational effects, but managing them. The virtue of functional programming is that most of what happens is stateless, so that when stateful effects are needed, they're simple, uncoupled, and do exactly what one expects them to do.

One principle for these sorts of applications is the "command/query separation". In essence, a procedure exposed at an interface level should either (a) do something, and do exactly what the user expects, or (b) answer a question about the system without computational effects. The case of (a) might be called "side effects", but that's a misnomer in this case because the requested state changes are the main effects.

Functional programming is really about having the most basic "building block" of a program be a referentially-transparent function, and to handle stateful effects either using an abstraction such as an Action DSL (which represents a collection of stateful behaviors, which are computed statelessly and then executed or "committed" in a stateful environment, relegating the statefulness to one "commit" function) or a monadic representation. In contrast, imperative programming uses computational actions as the atomic "building block".

For small programs, either imperative or functional programming will work. Imperative programming isn't evil; sometimes (but not often) it's the right approach. The problem emerges as programs get larger and more complicated, and more hands pass over the code. If a function is referentially-transparent, its semantics are usually tractable. If proper tests exist for it, optimizing the function (e.g. for performance) shouldn't affect the rest of the system. The constraint to which the function's maintainer is held is that the semantics shouldn't change, and any alteration that does change its semantics is an objective bug. The function might be memoized (that's a private "side effect" that doesn't affect referential transparency) but it still has to behave the same way. That's a really good property. In an imperative language where functions are replaced by computational actions, these actions are frequently made more complex and featureful as time goes on, and unexpected interactions emerge. Semantics can be defined for imperative programs, but doing so is more complicated and it's rarely done in real-world professional programming.

Most clusterfuck awful systems in software, for the record, are complex systems with imprecise semantics. I would say that 90% of software failures I've seen can be characterized in this way. This sort of thing can happen in any language, but it seems to be a lot more common in imperative programming, especially in verbose languages like Java or C++. It's just much harder to enforce precise semantics over an anything-goes imperative style of programming than a functional one.

As for databases... well, reality is that purely functional programming isn't always possible. We're not just computing mathematical functions; we're trying to do things in the physical world. We need persistence, and that means telling a robotic arm to write bytes on magnetic media (stateful effect). There are several approaches that one can use. One that I like (and it has a lot of desirable properties in distributed systems) is the append-only approach: update and delete operations simply aren't allowed, although new records can shadow old ones in the context of time-sensitive queries. For example, the temperature in New York is 56 degrees now (8:30 am, May 7). In six hours, it might be 70. That doesn't mean we throw the old record away. It's still valid: the temperature was 56 at 8:30. The answer to the query, "What was the temperature at 8:30 in New York?" should never change. On the other hand, the answer to the query, "What is the most recent temperature in New York?" will change all the time.