April 23, 2013 at 9:52 am
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?
After the data portion of the backup a full and differential backup then backup enough of the tlog to restore the database to a consistant state. IIRC this means any transactions that completed prior to the end of the data portion of the backup. I'm sure Gail can provide a much better and detailed answer.
April 23, 2013 at 10:01 am
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
April 23, 2013 at 10:32 am
Also, remember that a full backup will reset the page differential bitmaps and increment the Differential base LSN too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 23, 2013 at 11:20 am
GilaMonster (4/23/2013)
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.
Yeah, sorry, I grabbed that one immediately and didn't notice the subsequent edit.
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.
I assume then, that the reading of log records is a one-time event? That is, after doing the primary read, it catches up with the log records once, and doesn't continually go back and check for more transactions that may have accumulated while it was catching up the previous transactions?
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)
So, short of doing a restore to someplace safe and examining the data manually, there's no real way of knowing at exactly what point the backup captured the database, except that it is internally consistent?
April 24, 2013 at 1:13 am
pdanes (4/23/2013)
I assume then, that the reading of log records is a one-time event? That is, after doing the primary read, it catches up with the log records once, and doesn't continually go back and check for more transactions that may have accumulated while it was catching up the previous transactions?
It doesn't need to. The data backup plus the additional log records are sufficient to ensure you have a transactionally consistent database, which is the important thing as far as SQL server (and the user, for that matter!) is concerned.
April 24, 2013 at 3:15 am
pdanes (4/23/2013)
I assume then, that the reading of log records is a one-time event? That is, after doing the primary read, it catches up with the log records once, and doesn't continually go back and check for more transactions that may have accumulated while it was catching up the previous transactions?
The backup process reads the data files (extent at a time), after finishing it reads and adds to the backup file all the log records from time that the backup started or oldest open transaction at the time the backup completed, whichever is older (I incorrectly said 'started' earlier) to the last log record at the time that the data copying portion completed. Since that is done after the data copying portion completed, that's a fixed portion of the log and can't change further (no need to back up log records that were added while backing up log records, they're not needed for a consistent restore).
So, short of doing a restore to someplace safe and examining the data manually, there's no real way of knowing at exactly what point the backup captured the database, except that it is internally consistent?
You can pretty much say it's the time that the backup completed, because unless you have some odd case where half the database changes during the backup, the data copying portion of the backup is the vast majority of the time.
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
April 25, 2013 at 10:48 am
just for me to understand the point, so when there is atrasaction log backup will can not do differential backup at the same time or what
April 25, 2013 at 3:31 pm
GilaMonster (4/24/2013)
You can pretty much say it's the time that the backup completed, because unless you have some odd case where half the database changes during the backup, the data copying portion of the backup is the vast majority of the time.
Ok, thanks for the info. Some of these discussions sure get involved, considering how simple a question sets them off.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply