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 «««12345»»

Restart Log Backups Expand / Collapse
Author
Message
Posted Wednesday, July 04, 2012 11:30 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 @ 4:42 PM
Points: 41,516, Visits: 34,431
codebyo (7/4/2012)
Shouldn't this be:
"Take either a full AND differential backup and then a log backup"?


No. You;ve switched the DB to simple recovery and back to full. Unless there has never ever, ever been a full backup taken (and if that were the case there couldn't have ever been any log backups running), all you need to do to restart the log chain is take a diff backup.

Sure, the diff's useless without the associated full, but databases that are important enough to get log backups get regularly scheduled full backups that are kept in safe places?



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 #1325098
Posted Wednesday, July 04, 2012 2:24 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 10:36 AM
Points: 896, Visits: 1,479
Thank you for the explanation, Gail.
I've never tried that myself and was wondering.


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1325126
Posted Wednesday, July 04, 2012 5:39 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 10:36 AM
Points: 896, Visits: 1,479
Steve Jones - SSC Editor (6/28/2012)
Glad you liked it, but I can't take credit for discovering this. Mr Randal, of SQL Skills, taught me this. Now I really want to get to one of his Immersion classes.


Same here. I learn a lot from Paul, Kimberly and Jonathan's posts (and also yours, Steve). There are no better teachers than those found in this community.


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1325139
Posted Monday, July 09, 2012 4:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 24, 2014 8:52 AM
Points: 28, Visits: 141
I may be biased. But, it's a bit of crap question. As there are 2 "right" answers. One "busts the myth", but the other is also correct. What is more, I must have seen about 2 dozen cases of "never been backed up", versus a couple of - "just do a differential". The question doesn't actually imply that the database has ever had any kind of successful backup. So unless you've checked and can rely on already existing full backup - "just do a differential" would probably also fail.

This DBA says - "It depends".
Post #1326739
Posted Monday, July 09, 2012 4:38 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 @ 4:42 PM
Points: 41,516, Visits: 34,431
SQLBoar (7/9/2012)
The question doesn't actually imply that the database has ever had any kind of successful backup. So unless you've checked and can rely on already existing full backup - "just do a differential" would probably also fail.


If the database had never had a full backups, then there could never have been working log backups and the log would not grow if the (non-existent) log backups failed for a period of time.



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 #1326745
Posted Monday, July 09, 2012 5:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 24, 2014 8:52 AM
Points: 28, Visits: 141
As it says "My sales database had a problem with log backups failing and the log file grew large. I do the following: ". It doesn't say that log backups ever worked, so all you can assume is "log backups failing". And a reason for that would be .. .

P.S. Gail thanks for the prompt reply - I was dead impressed.


This DBA says - "It depends".
Post #1326760
Posted Monday, July 09, 2012 6:03 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 @ 4:42 PM
Points: 41,516, Visits: 34,431
Well, personally I would assume that if someone states that there is a problem with log backups failing now, they worked at some point, it would be really weird to assume failing now = never worked at all. Also, failure of log backups would not cause the log to grow if there had never ever been a full backup.

If the switch to simple did allow the log to be shrunk, then it implies that the lack of log backups was responsible for making the log grow, which would hence imply that the log was being retained for backups which only happens if there was a full backup at some point.

You can nitpick any question and argue about what's implied/meant/stated. It's why I never write QotD any longer.



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 #1326791
Posted Monday, July 09, 2012 7:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 24, 2014 8:52 AM
Points: 28, Visits: 141
Gail, totally sympathise there. For a long time I kind of steered clear of the temptation to answer the questions (or post anything).

Then I did a few, to come only across one that was just a bit annoying. It wouldn't have been so annoying if I'd only just last month dealt with 4 "Oh, we don't bother to backup THOSE databases" databases (out of 30), on a newly inherited database server. OK, they were doing all the right things for the 2TB customer databases, and weren't doing anything with these 4 "small ones", but .. . It does happen, to quote my new boss "IT's IT sometimes **IT happens" (that was a comment in response to a windows server patch problem - not SQL Server).

I never really posted much stuff for the same reason. Well that and I spent 5 years at an organisation that really looked dimly on anyone posting anything publicly. Or, having: facebook, or linked-in , or .. accounts .

Sorry, I've now gone very off-topic. And, I think people like you, who put yourself out there amongst the flames, etc.., are very public spirited. So I'll stop whinging. Let's face it - would I have nit-picked if I'd clicked on the other button ?




This DBA says - "It depends".
Post #1326844
Posted Monday, July 09, 2012 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 5,793, Visits: 8,002
GilaMonster (7/9/2012)
It's why I never write QotD any longer.

And that is really a loss. I can understand yoour feelings. Sometimes, the comments are fair. Sometimes, they are far-fetched. And sometimes, it appears as if posters only try to find excuses to not having to admit that they didn't know something.

I've learned to grow a skin. I read the comments on my questions, agree and try to improve the next question if I feel the comments are fair, and (usually) just shrug and move on with my life if I think someone is nitpicking.

I hope you'll reconsider and resume submitting QotD's.

Cheers,
Hugo



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1326862
Posted Monday, July 09, 2012 9:41 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 10:36 AM
Points: 896, Visits: 1,479
Hugo Kornelis (7/9/2012)
I hope you'll reconsider and resume submitting QotD's.


+1


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1326936
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse