SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction Log Backups Question


Transaction Log Backups Question

Author
Message
defyant_2004
defyant_2004
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 146
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90593 Visits: 45284
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

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


defyant_2004
defyant_2004
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 146
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90593 Visits: 45284
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

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


defyant_2004
defyant_2004
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 146
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90593 Visits: 45284
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

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


defyant_2004
defyant_2004
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 146
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?
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6780 Visits: 7394
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; They'll drag you down to their level and beat you with experience" ;-)
defyant_2004
defyant_2004
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 146
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?
Lynn Pettis
Lynn Pettis
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: 40888 Visits: 38567
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
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