Some of my coworkers were surprised when I told them that I can back up an SQL Server database while it's still running and wondered how that's possible. I know that SQL Server is capable of backing up a database while it is still online but I'm not sure how to explain why it's possible. My question is what effect does this have on the database?
If data is modified (by an insert, update, or delete) while the backup is running, will the backup contain those changes or will it be added to the database afterwards?
I'm assuming that the log file plays an important role here but I'm not quite sure how.
edit: Just as a note, my case involves backing up the databases using SQL Server Agent and the effects of database modifications during this process.