>How does a SQL backup process function when multiple users are hitting the SQL database?

By backing up the file up to that point - the transactions aren't really written into the database per se, anyway, they're written into the transaction log and replayed when the service starts (or on first access - dunno). Backup backs up the database as is, plus transaction log up to the point when it was started. Any transactions that happen during the backup are just appended to the end of the log. Any queries are taken from a snapshot somewhere, I think.

This is how I understand the theory; haven't really looked under the hood.