pdanes (4/23/2013)
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?
No. The backup reads the database an extent at a time, beginning to end. Once that is done, it adds into the backup the log records starting at the point of the earliest open transaction before the backup started and ending at the point that the data reading portion of the backup completes.
You write here that the database is consistent as of the completion time. Does consistent mean as it is at exactly that instant?
No, I said 'close to the end'. Specifically at the point that the data reading portion of the backup completes. The entire database will be consistent to that time.
What happens if a heavily loaded server keeps changing the database, forcing a do-over, faster than the backup process can ever complete?
There's no do-overs, so the question is moot.
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?
Nope, that's not going to result in a transactionally consistent database (which restores always must)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability