April 16, 2014 at 12:07 pm
Hello,
We have a scheduled full backup set for midnight and 30 minute log backups throughout the day.
Sometimes our developers trigger there own unscheduled backups of these databases throughout the day.
Won't this break the chain and hurt our ability to use the log backups?
Thanks
April 16, 2014 at 12:51 pm
Full backups do not and never have broken the log chain.
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 16, 2014 at 1:16 pm
Thank you for the reply.
Let me throw another scenario. What if someone took an unscheduled LOG backup during that period.
Would that unscheduled log backup break the cycle?
April 16, 2014 at 2:00 pm
Break it, no. However to restore you'd need that log backup file.
I can understand devs taking ad-hoc full backups (though they shouldn't have sufficient rights on production to do that), but why would a dev take a single log 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 16, 2014 at 3:49 pm
It appears they were attempting to truncate the log on a failed script they wrote.
Is there a way to query SQL Server to verify all the log backups are available to a for recently performed full backup? For example, if someone was to run an adhoc log backup to an unknown location, it appears there is no way to know that there was a missing log file.
April 16, 2014 at 4:01 pm
Time to give devs a training course on SQL admin.
Backup history is in MSDB backuphistory table.
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 17, 2014 at 10:15 am
Thanks for answering the question. I would like to run one more thing by you just to clear up something in my mind.
If we have log backups taken every hour, but somewhere along the line lose the original full database backup. Can we just a take another full database backup? Will the "very next scheduled" log backup reset the chain to this new database backup or do we need to issue a special command to tell SQL Server this full backup is the new starting point?
April 17, 2014 at 10:22 am
Yes you can simply take another full backup. Full backups do not affect the LSN chain for log backups in any way (except for the very first full backup ever taken).
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 17, 2014 at 10:37 am
Thank you. Is the following safe to assume?
All log files up to the point of the NEW FULL database backup are unusable and could be deleted. Even the though the log chain is still intact, the log backup immediately following the full database backup is the new starting point? (Log files before the new full database backup can't be applied)
Also, there is no command or recording within SQL Server for this. You just have to look at the date/time of the log backup to make sure it falls after the New Full database backup?
is that correct?
April 17, 2014 at 10:59 am
defyant_2004 (4/17/2014)
Thank you. Is the following safe to assume?All log files up to the point of the NEW FULL database backup are unusable and could be deleted. Even the though the log chain is still intact, the log backup immediately following the full database backup is the new starting point? (Log files before the new full database backup can't be applied)
Also, there is no command or recording within SQL Server for this. You just have to look at the date/time of the log backup to make sure it falls after the New Full database backup?
is that correct?
No. They are usable with full backups taken prior to the most current full backup. Also, if the most current full backup file were to become corrupt or lost, and you still have a previous full backup and ALL log backups since that backup, you could still do a point in time restore. The one thing that really breaks a log chain (besides switching to and from simple recovery model from either Bulk Logged or Full recovery models) is losing one of the log backups.
April 17, 2014 at 12:36 pm
I appreciate the information. It all makes perfect sense. The very first log backup to apply is the one IMMEDIATELY following full database backup. The chain is unaffected by full backups, however, the starting log backup that is restored is the one immediately following that backup used for restore.
I take it the only to know which log backup to apply is by looking at the date time that log back up was created.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply