July 4, 2012 at 11:30 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2012 at 2:24 pm
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/MCSE/MCSA
July 4, 2012 at 5:39 pm
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/MCSE/MCSA
July 9, 2012 at 4:29 am
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".
July 9, 2012 at 4:38 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2012 at 5:05 am
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".
July 9, 2012 at 6:03 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2012 at 7:19 am
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 :cool:.
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".
July 9, 2012 at 7:54 am
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
July 9, 2012 at 9:41 am
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/MCSE/MCSA
July 9, 2012 at 11:18 am
codebyo (7/9/2012)
Hugo Kornelis (7/9/2012)
I hope you'll reconsider and resume submitting QotD's.+1
Square that. 🙂
Errrr.... double that!
Rich
July 9, 2012 at 11:29 am
Hugo Kornelis (7/9/2012)
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
To add a little more to what other people have said, based on what I've seen from your posts helping people out I would love to see some questions from you. We can always use more well thought out questions here and I'm betting any question you would let yourself submit would fall under that.
August 8, 2012 at 12:10 pm
Good learning point, thanks.
September 5, 2012 at 6:30 am
Good question and explanation
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply