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
SQL_Bob
SQL_Bob
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 408
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
Twitter: @SQLBob
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226897 Visits: 46335
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

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


paul.knibbs
paul.knibbs
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4244 Visits: 6240
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!
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19383 Visits: 10042
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147668 Visits: 19440
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.

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
pdanes
pdanes
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1788 Visits: 1354
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.
Orlando Colamatteo
Orlando Colamatteo
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: 40126 Visits: 14413
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
pdanes
pdanes
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1788 Visits: 1354
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226897 Visits: 46335
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

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


pdanes
pdanes
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

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