The example you give actually is a $\sigma$-finite Borel measure. Equip $[0,1]$ with the cofinite topology (in which a set is open iff it is either empty or its complement is finite). Then your $\Sigma$ is the Borel $\sigma$-algebra of the cofinite topology (it is a nice exercise to verify this).
However, there is the following result:
Proposition. Let $(X,d)$ be a separable metric space, $\Sigma$ its Borel $\sigma$-algebra, and $\mu$ a $\sigma$-finite measure on $\Sigma$. Then each atom of $\mu$ is the union of a point mass and a null set.
Proof. Let $C$ be a countable dense subset of $X$. For each integer $k\in\mathbb{N}$, we have $\bigcup_{x \in C} B(x, 1/k) = X$. Thus $\bigcup_{x \in C} (A \cap B(x,1/k)) = A$. So by countable additivity, there exists $x_k \in C$ such that $\mu(A \cap B(x_k, 1/k)) > 0$. Since $A$ is an atom, $\mu(A \setminus B(x_k, 1/k)) = 0$. Let $S = \bigcap_k B(x_k, 1/k)$.
Since for each $k$, $S$ is contained in a ball of radius $1/k$, $S$ contains at most one point.
On the other hand, by De Morgan's law and countable additivity,
$$\mu(A \setminus S) = \mu\left(\bigcup_k A \setminus B(x_k, 1/k)\right) = 0.$$
Since $\mu(A\cap S)=\mu(A) > 0$, $A\cap S$ is not empty, so $A\cap S$ is a singleton.
Hence $A\cap S$ is a point mass and $A \setminus S$ a null set. $\Box$
So in this case, effectively the only atoms are point masses.
Note that we did not need to assume $X$ was complete.
For non-separable metric spaces, things are harder. For uncountable discrete spaces (which are certainly metric), the question of whether there can be nontrivial atoms is related to whether the cardinality of $X$ is a measurable cardinal, and such questions tend to be independent of the axioms of ZFC. I asked a new question about it: Consistency strength of 0-1 valued Borel measures.
