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 12»»

Transaction Log Backups Question Expand / Collapse
Author
Message
Posted Wednesday, April 16, 2014 12:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:03 PM
Points: 47, 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
Post #1562407
Posted Wednesday, April 16, 2014 12:51 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
Full backups do not and never have broken the log chain.


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 #1562420
Posted Wednesday, April 16, 2014 1:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:03 PM
Points: 47, 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?
Post #1562426
Posted Wednesday, April 16, 2014 2:00 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
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 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 #1562442
Posted Wednesday, April 16, 2014 3:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:03 PM
Points: 47, 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.
Post #1562489
Posted Wednesday, April 16, 2014 4:01 PM This worked for the OP Answer marked as solution


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
Time to give devs a training course on SQL admin.

Backup history is in MSDB backuphistory table.



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 #1562491
Posted Thursday, April 17, 2014 10:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:03 PM
Points: 47, 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?
Post #1562750
Posted Thursday, April 17, 2014 10:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 3,998, Visits: 7,172
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"
Post #1562755
Posted Thursday, April 17, 2014 10:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:03 PM
Points: 47, 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?
Post #1562770
Posted Thursday, April 17, 2014 10:59 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,808, Visits: 32,746
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.



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)
Post #1562782
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse