SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log backups while Full/Differential backups are running


Log backups while Full/Differential backups are running

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89379 Visits: 45284
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 (well, very close to the end).
What did I say that implied your conclusion?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


pdanes
pdanes
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 1354
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?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64505 Visits: 19117
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?


When the data reading portion finishes. All log records to that point are captured. When is that? Depends on lots of stuff.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40426 Visits: 38567
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?


Actually Gail said the last full backup that COMPLETED prior to the START of the log backup(s).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
pdanes
pdanes
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 1354
Lynn Pettis (4/23/2013)
pdanes (4/23/2013)
GilaMonster (4/23/2013)
[quote]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?


Actually Gail said the last full backup that COMPLETED prior to the START of the log backup(s).

So she did, and I don't think I implied otherwise in my subsequent questions. Naturally, a backup that hasn't completed is of no use to anyone. I don't understand your point.
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40426 Visits: 38567
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89379 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20378 Visits: 17244
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" ;-)
pdanes
pdanes
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 1354
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?
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2607 Visits: 6232
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search