February 28, 2011 at 10:59 am
Never had a "set-in-stone" answer on this one and I've, fortunately, never had to find out the hard way, but if a transaction log backup job fails, for any reason, does this failure constitute a "break in the chain" of t-log backups?
Please advise...thanks!
February 28, 2011 at 1:34 pm
It kind of depends on the type of log backup failure, but yes, some of them will break the recovery chain. You'll pretty much know the next time you go to run the log backup. If it fails, you're in a broken chain situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2011 at 1:39 pm
OK, then for example, the disk array that my t-log backups are pointed to will be unavailable for approx. 30-mins for updates to be applied. If they are scheduled for backups every 10-mins, will the subsequent t-log backup that will occur after the array is available backup all transactions since the last good t-log backup and, therefore, not break the chain? Or will all be lost and I should do a full backup at that time? I hope I am explaining my concern clearly...
February 28, 2011 at 2:38 pm
If you just put a delay in the transaction log backups during that period you can avoid the issue. That's what I'd do. Otherwise, no, I don't think a driving being compltely unavailable will cause the backup chain to break. You will get a failure, but you won't lose the chain because of that error.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2011 at 3:52 pm
There is a very small possibility that you could be in the middle of a transaction log backup - and the drive becomes unavailable just at the right time to cause that backup file to be corrupted.
This would cause the backup to appear to be successful - but, your log chain would still be broken.
Grant's advise on suspending the transaction log backups during this time is the best way to avoid that kind of issue - or any issues with the log chain.
If the drive is not available when the log backup starts, it will fail but it will not cause a break in the log chain. The real concern here is that you are not in the process of performing a log backup when the drive become unavailable - which could cause a break in the log chain and/or a corrupted backup file (which also breaks your log chain).
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
February 28, 2011 at 4:00 pm
Jeffrey Williams-493691 (2/28/2011)
There is a very small possibility that you could be in the middle of a transaction log backup - and the drive becomes unavailable just at the right time to cause that backup file to be corrupted.This would cause the backup to appear to be successful - but, your log chain would still be broken.
Grant's advise on suspending the transaction log backups during this time is the best way to avoid that kind of issue - or any issues with the log chain.
If the drive is not available when the log backup starts, it will fail but it will not cause a break in the log chain. The real concern here is that you are not in the process of performing a log backup when the drive become unavailable - which could cause a break in the log chain and/or a corrupted backup file (which also breaks your log chain).
good catch. I hadn't thought of that. It might break the chain in that event.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2011 at 8:15 pm
Yes - and that is the problem...it might break the log chain, but no way to be sure. So, to be safe - I would not recommend running transaction log backups during the outage or I would modify the location to another drive temporarily.
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
November 9, 2012 at 2:06 pm
Hi i have some posible options in my hand that is
*With out taking full backup we cant take log backup so the full backup was fail automaticaly log backup also fail
*Changing the recovery model from full to simple
*when database goes into suspect mode\offline
*when the disk space is full
December 4, 2012 at 3:02 am
hi
yes it will break the chain.
t-log backup job failure break t-log chain
below are different reason,
1st if any one taken full backup of same database where t-log job is running,in this case u want take full backup dont want to break chain of t-log then use copy_only backup
2nd if anyone change recovery model of database
3.if someone executed backup log with truncate_only
in this case you can use trace flag 3031 ,3231 it wont break t-log backup chain
January 16, 2013 at 6:34 am
Hi,
1st if any one taken full backup of same database where t-log job is running,in this case u want take full backup dont want to break chain of t-log then use copy_only backup
--> i thought full backup and transaction logs backup are independent? full backup shouldn't break the chain of t log?
thanks
January 16, 2013 at 8:57 am
It's hard to understand if you are quoting something or stating something.
Full backups do not affect or break the log chain. The full backup can include part of the log, and reduce the need for a specific log backup (potentially) on the restore, but it doesn't affect the log chain.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply