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 Sunday, April 21, 2013 3:08 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:31 PM
Points: 41, Visits: 398
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
Post #1444823
Posted Sunday, April 21, 2013 3:18 PM


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 @ 7:43 AM
Points: 40,438, Visits: 36,894
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 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 #1444824
Posted Monday, April 22, 2013 6:56 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 1,625, Visits: 5,707
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!
Post #1444960
Posted Monday, April 22, 2013 1:54 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:14 PM
Points: 4,363, Visits: 9,545
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1445152
Posted Monday, April 22, 2013 2:07 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,284, Visits: 15,748
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
Post #1445158
Posted Tuesday, April 23, 2013 7:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 8:06 AM
Points: 346, Visits: 1,029
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.
Post #1445404
Posted Tuesday, April 23, 2013 7:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 7,135, Visits: 12,746
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
Post #1445429
Posted Tuesday, April 23, 2013 8:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 8:06 AM
Points: 346, Visits: 1,029
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.
Post #1445452
Posted Tuesday, April 23, 2013 8:22 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 @ 7:43 AM
Points: 40,438, Visits: 36,894
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 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 #1445463
Posted Tuesday, April 23, 2013 8:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 8:06 AM
Points: 346, Visits: 1,029
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?
Post #1445472
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse