Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Log backups while Full/Differential backups are running Expand / Collapse
Author
Message
Posted Tuesday, April 23, 2013 8:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
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 2008, MVP
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

Post #1445479
Posted Tuesday, April 23, 2013 9:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 24, 2014 3:38 PM
Points: 336, Visits: 942
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?
Post #1445504
Posted Tuesday, April 23, 2013 9:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:27 PM
Points: 33,267, Visits: 15,434
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
Post #1445508
Posted Tuesday, April 23, 2013 9:46 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:47 PM
Points: 23,396, Visits: 32,229
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).



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)
Post #1445533
Posted Tuesday, April 23, 2013 9:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 24, 2014 3:38 PM
Points: 336, Visits: 942
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.
Post #1445537
Posted Tuesday, April 23, 2013 9:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:47 PM
Points: 23,396, Visits: 32,229
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.



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)
Post #1445538
Posted Tuesday, April 23, 2013 10:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
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 2008, MVP
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

Post #1445552
Posted Tuesday, April 23, 2013 10:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 6,350, Visits: 13,675
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"
Post #1445567
Posted Tuesday, April 23, 2013 11:20 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 24, 2014 3:38 PM
Points: 336, Visits: 942
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?
Post #1445596
Posted Wednesday, April 24, 2013 1:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 1,639, Visits: 5,619
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.
Post #1445789
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse