April 21, 2013 at 3:08 pm
A few weeks ago I was at an event where someone came up and asked me if it's bad to perform transaction log backups while full/differential backups are running.
I said I couldn't come up with any reason why it would be "bad", but it's important to note that:
- VLFs will not be marked as inactive by a log backup while a full/diff backup is running
- There can be increased I/O impact due to multiple backup processes running concurrently
They then explained that their supervisor has handed down an edict that no transaction log backups can run while full backups are running. They asked if I could explain my thoughts to their supervisor, who was also present at the event. I tried. They blew me off and said that "I'm never going to need to use a transaction log backup that was taken while a full or differential backup was running, so why bother creating them?"
I thought for a second, and my replies at this point were:
- The longer you wait in-between transaction log backups, the longer they will take to create
- You may find yourself needing those log backups if you have to do a point-in-time restore to a time that a full or differential backup was running.
The supervisor told me I was just "making stuff up." I said I'm sorry they felt that way, and left it at that.
This exchange left me feeling absolutely certain that I never want to work for that person. Ever. It also left me curious as to if I was really right or not.
Does anyone have any comments either for or against my replies in this situation? I'd be interested in hearing any and all input.
_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008
Blog: The Outer Join[/url]
Twitter: @SQLBob
April 21, 2013 at 3:18 pm
Let's see... what happens when we have a 6-hour full backup duration and a 15 minute data loss SLA and log backups don't run during that full backup?
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 22, 2013 at 6:56 am
Seems to me that when this guy is accusing you of making stuff up he's basically saying "I don't understand what you just said". I agree with you and Gail here--there's no rule that says "failures can't happen during backups", and if one does, he'll be wishing he had that last log backup available!
April 22, 2013 at 1:54 pm
GilaMonster (4/21/2013)
Let's see... what happens when we have a 6-hour full backup duration and a 15 minute data loss SLA and log backups don't run during that full backup?
Worse...what happens when the system crashes 5 minutes before the end of that 6 hour backup and you completely lose the drive where the mdf and/or ldf files are located?
Oops...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 22, 2013 at 2:07 pm
Jeffrey Williams 3188 (4/22/2013)
GilaMonster (4/21/2013)
Let's see... what happens when we have a 6-hour full backup duration and a 15 minute data loss SLA and log backups don't run during that full backup?Worse...what happens when the system crashes 5 minutes before the end of that 6 hour backup and you completely lose the drive where the mdf and/or ldf files are located?
Oops...
Good resume, good backup. You only need one.
April 23, 2013 at 7:08 am
GilaMonster (4/21/2013)
Let's see... what happens when we have a 6-hour full backup duration and a 15 minute data loss SLA and log backups don't run during that full backup?
Smart-assery aside, I think it's still a legitimate question. What state are such backups in when they are taken? What does a transaction log backup give you when it's taken during a full or differential backup? Where do you start and what do you use if you need to restore such a database? It never occurred to me, since I have such small datasets (cca. 100MB) that it's not an issue - even full backups run in less than a minute, and I have them scheduled for late at night, when the server and database are completely idle. I have it simple - nightly full, hourly diff and quarter-hourly trans, the last two only during working hours, with checks in the code to see if the database changed during the preceding appropriate interval. No possibility of overlap, so restore (and yes, I try it regularly to be sure it works) is a simple matter of stepping up to the point of failure. But I have no idea how I would deal with overlapping schedules.
I have Shawn McGehee's SQL Server Backup and Restore book, but I don't recall that issue being addressed in it. I wasn't specifically looking for that when I read it, though, so I may have missed it.
April 23, 2013 at 7:43 am
I agree with SQLBob in that I would never want to work for that person. Maybe someone can get this thread under their nose...maybe they'll listen to a chorus.
If I did happen to find this person as my supervisor then it would be clear that the person needing their resume to be dusted off would be my supervisor, although that would not stop me from continuing to take log backups during full or diff runs because I have a suspicion that a person like that would never cop to their own edict and instead would invoke a resume-generation-event for me. I would be fine saving theirs and my job in the process, and potentially the company's behind too, despite the ridiculousness of it all.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2013 at 8:10 am
opc.three (4/23/2013)
I agree with SQLBob in that I would never want to work for that person. Maybe someone can get this thread under their nose...maybe they'll listen to a chorus.If I did happen to find this person as my supervisor then it would be clear that the person needing their resume to be dusted off would be my supervisor, although that would not stop me from continuing to take log backups during full or diff runs because I have a suspicion that a person like that would never cop to their own edict and instead would invoke a resume-generation-event for me. I would be fine saving theirs and my job in the process, and potentially the company's behind too, despite the ridiculousness of it all.
Good tactic for such things is to get it in writing, or email. You'll probably not have any luck getting them to actually write out instructions to the effect of "... don't make backups ..." - even toads like this supervisor aren't usually that stupid, but a cleverly worded question can often trap them into making a statement that will later cover your a$$. Especially effective is if you can wave such a paper, completely exonerating you from blame, wait for crap to get firmly nailed to the offending PHB, and only then trot out the fact that you actually saved the day by disobeying his instructions.
He may be out the door, or if it's some big boss's relative, you'll be out the door instead, but either way, it'll get settled quickly.
April 23, 2013 at 8:22 am
pdanes (4/23/2013)
What state are such backups in when they are taken?
The same state they would be in if they were taken without a full/diff running
What does a transaction log backup give you when it's taken during a full ordifferential backup?
A log backup file containing all the log records since the last log backup, same as it would if it were running at any other time
Where do you start and what do you use if you need to restore such a database?
A full backup that completed before that log backup started and an unbroken chain of log backups from the full backup up to the point you need to restore to.
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 8:30 am
GilaMonster (4/23/2013)
pdanes (4/23/2013)
What state are such backups in when they are taken?The same state they would be in if they were taken without a full/diff running
What does a transaction log backup give you when it's taken during a full ordifferential backup?
A log backup file containing all the log records since the last log backup, same as it would if it were running at any other time
Where do you start and what do you use if you need to restore such a database?
A full backup that completed before that log backup started and an unbroken chain of log backups from the full backup up to the point you need to restore to.
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?
April 23, 2013 at 8:35 am
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
April 23, 2013 at 9:07 am
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?
April 23, 2013 at 9:10 am
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.
April 23, 2013 at 9:46 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?
Actually Gail said the last full backup that COMPLETED prior to the START of the log backup(s).
April 23, 2013 at 9:50 am
Lynn Pettis (4/23/2013)
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?
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.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply