pdanes (4/23/2013)

---

GilaMonster (4/23/2013)

---

pdanes (4/23/2013)

---

So a backup is a 'snapshot' from the moment in time that the backup process was initiated? And operations performed during the backup process do not get incorporated?

No, not at all. A backup (full or differential) is consistent as of the time that the backup completes.

What did I say that implied your conclusion?

The fact that you wrote a restore would use the last full backup, plus all the log backups from there to the desired restore point. That seemed to me to imply that the backup was a point-in-time instant. But now that I think about it a bit more, I guess that doesn't really follow.

What I was thinking overall was that a backup could catch come process in the middle of messing with something, and so capture the database in a disorganized state. I realize this is getting away from the question of simultaneous transaction and full backups, but these forum topics have a way of dredging up additional items to ponder. Probably the short answer is that it's up to the application to use transactions in such a way that a disorganized state is not possible (I have to believe that the backup process respects the integrity of a transaction), but let me see if I can create a scenario that illustrates the question in a sensible way.

Suppose there is a backup process that takes thirty minutes, and lights off at 8 sharp. TableA is backed up at the beginning of the process (8:00), TableZ at the end (8:30). No problem so far, but what happens if a process updates both TableA and TableZ at 8:15? Does the backup process go back and 'redo' TableA?

You write here that the database is consistent as of the completion time. Does consistent mean as it is at exactly that instant? What happens if a heavily loaded server keeps changing the database, forcing a do-over, faster than the backup process can ever complete?

Or do you mean as each table was at the time the backup process got to it? If so, how do you know what 'state of the database' is in your full backup, if it spans a long time interval?