InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 13:37:49 GMT20http://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxA really good one. :-)Tue, 18 Dec 2012 04:37:05 GMTganeshkumar005http://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxGood question and explanationWed, 05 Sep 2012 06:30:24 GMTRobin Sassonhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxGood question indeedWed, 22 Aug 2012 00:59:34 GMTokbangashttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxGood learning point, thanks.Wed, 08 Aug 2012 12:10:24 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspx[quote][b]Hugo Kornelis (7/9/2012)[/b][hr][quote][b]GilaMonster (7/9/2012)[/b][hr]It's why I never write QotD any longer.[/quote]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[/quote]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.Mon, 09 Jul 2012 11:29:26 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspx[quote][b]codebyo (7/9/2012)[/b][hr][quote][b]Hugo Kornelis (7/9/2012)[/b][hr]I hope you'll reconsider and resume submitting QotD's.[/quote]+1[/quote]Square that. :-)Errrr.... double that!RichMon, 09 Jul 2012 11:18:01 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspx[quote][b]Hugo Kornelis (7/9/2012)[/b][hr]I hope you'll reconsider and resume submitting QotD's.[/quote]+1Mon, 09 Jul 2012 09:41:51 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspx[quote][b]GilaMonster (7/9/2012)[/b][hr]It's why I never write QotD any longer.[/quote]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,HugoMon, 09 Jul 2012 07:54:57 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxGail, 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 ?Mon, 09 Jul 2012 07:19:36 GMTSQLBoarhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxWell, 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.Mon, 09 Jul 2012 06:03:52 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxAs 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.Mon, 09 Jul 2012 05:05:13 GMTSQLBoarhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspx[quote][b]SQLBoar (7/9/2012)[/b][hr]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.[/quote]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.Mon, 09 Jul 2012 04:38:09 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxI 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.Mon, 09 Jul 2012 04:29:30 GMTSQLBoarhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspx[quote][b]Steve Jones - SSC Editor (6/28/2012)[/b][hr]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.[/quote]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.Wed, 04 Jul 2012 17:39:18 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxThank you for the explanation, Gail.I've never tried that myself and was wondering.Wed, 04 Jul 2012 14:24:03 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspx[quote][b]codebyo (7/4/2012)[/b][hr]Shouldn't this be:"Take either a full AND differential backup and then a log backup"?[/quote]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?Wed, 04 Jul 2012 11:30:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxInteresting question! Thank you!But I was confused with the alternative:"Take either a full OR differential backup and then a log backup"Shouldn't this be:"Take either a full AND differential backup and then a log backup"?One question though: even if one could back up the log after only a differential backup, wouldn't that be useless without the full backup (the diff will not restore)?Wed, 04 Jul 2012 10:55:02 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxThanks for the clarification Lynn, it makes more sense in that context.Thu, 28 Jun 2012 18:38:02 GMTdavoscollectivehttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspx[quote][b]davoscollective (6/28/2012)[/b][hr]Normally I answer these without looking up BOL first but this time I thought I'd double check.Unfortunately this myth comes straight from the horse's mouth:"Before you can create the first log backup, you must create a full backup. "BOL: [url]http://msdn.microsoft.com/en-us/library/ms186865.aspx[/url][/quote]Took a bit to find this reference, but if I read it correctly they may be talking about when a database is first created. Based on the question, we could make a practical assumption that there was a full backup taken earlier.I created a new database and both a t-log backup and a differential backup failed. I needed to take a full backup before either of those worked.Restoring a full backup, i could take either after words.Thu, 28 Jun 2012 18:16:10 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxNormally I answer these without looking up BOL first but this time I thought I'd double check.Unfortunately this myth comes straight from the horse's mouth:"Before you can create the first log backup, you must create a full backup. "BOL: [url]http://msdn.microsoft.com/en-us/library/ms186865.aspx[/url]Thu, 28 Jun 2012 17:59:26 GMTdavoscollectivehttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxGreat question. If I wanted to start performing tlog backups on a database yesterday I would automatically run a full backup after setting to full recovery model. Today I know better.Thu, 28 Jun 2012 16:05:36 GMTDoug 53730http://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxGlad 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.Thu, 28 Jun 2012 14:36:19 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspx[quote][b]Drenlin (6/28/2012)[/b][hr]I have been looking at log backups this week so I was glad for this question to confirm what I have been reading and my understanding.However, there was no mentioning of the previous full backup in the question. Am I guessing that a similar error message would occur if attempting to perform a log backup if there was no full backup? A previous post only mentioned attempting a differential if there was no full backup.[/quote]If there's no full you won't be able to take a differential backup so that doesn't have to exist. I suppose it could be argued that since there was no statement that there was a full backup that a full may be required. However, in most people's environments it's a safe assumption (at least when it comes to answering a QotD) that a full backup exists.Thu, 28 Jun 2012 10:28:59 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxLearned something new, thanks SteveThu, 28 Jun 2012 10:25:08 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxNice one. Thanks Steve.Thu, 28 Jun 2012 10:18:06 GMTiBarhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxI have been looking at log backups this week so I was glad for this question to confirm what I have been reading and my understanding.However, there was no mentioning of the previous full backup in the question. Am I guessing that a similar error message would occur if attempting to perform a log backup if there was no full backup? A previous post only mentioned attempting a differential if there was no full backup.Thu, 28 Jun 2012 10:06:58 GMTDrenlinhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxDefinitely a gotcha!Thu, 28 Jun 2012 09:50:35 GMTLon-860191http://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxGood question Steve.Thu, 28 Jun 2012 09:46:57 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxThanks Steve.Thu, 28 Jun 2012 09:34:43 GMTOzYbOi d(-_-)bhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxI actually just dealt with this in a slightly different way. We're doing a purge on a large DB and the app owner wanted a quick way to roll back and re-establish the log chain if anything went wrong. The decision was to take a snapshot, restore from that if there was an issue, and then do a diff. I actually wrote an article about it but haven't submitted it yet because it's still being reviewed by someone else before I send it in.Thu, 28 Jun 2012 09:23:40 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxI was tempted with the first answer as it seemed correct. But a little bit of logic indicated me that there's no reason for the database backups to be incomplete so that it would be necessary to do a full backup. For the contrary, the log wouldn't be reliable after changing the recovery model from full to simple and back to full.Nice question, I'm glad to have read all the answers before making a choice.Thu, 28 Jun 2012 07:40:38 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxGood question, got this wrong though. Nice to have a myth busted.Thu, 28 Jun 2012 07:26:50 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxGot it wrong for the best possible reason: I learned something.Like many others, I thought a FULL backup was required to re-initiate the log chain, probably because that's what most recommendations say, following a switch of Full-to-Simple-to-Full recovery modes. Here's an example from a SQL MVP ([url=http://www.petri.co.il/sql-server-recovery-models.htm]http://www.petri.co.il/sql-server-recovery-models.htm[/url]):[quote]...if you’re in full mode and you want to truncate the log by switching to simple mode you’re free to do that. However, if you want to switch back to full mode and start taking log backups again, you’ll have to take a full backup before you can do that. [/quote]Great question, Steve!RichThu, 28 Jun 2012 07:23:27 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxI jumped the gun on this one and it seems I'm not alone in my actions. I just saw the first answer and was like "of course that's what you would do!" and then (X) incorrect. :( Excellent question Steve. I should have read all the available answers, but that first one just looked to tempting.Thu, 28 Jun 2012 06:55:04 GMTDana Medleyhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxOuch!!! Got me!!! - Excellent question, learned something new.Thu, 28 Jun 2012 06:43:44 GMTMike Hayshttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxI'm a bit annoyed with myself...I've read the linked article before, but I went ahead and clicked the "Full backup" option simply because that's what I would do in that situation, regardless of what's possible! Should have read the possible answers more carefully before clicking.Thu, 28 Jun 2012 06:24:06 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspx[quote][b]Duncan Pryde (6/28/2012)[/b][hr]I got led astray by the error message. Since it said there was no current database backup, I thought that meant you couldn't do a differential backup at all (since there was no full backup to "diff" against). Moral - error messages don't always say what they mean...[/quote]To follow on from this point, the message you get when performing a diff backup against a database where a full backup has not been taken is:Msg 3035, Level 16, State 1, Line 1Cannot perform a differential backup for database "xyz", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.Seems quite similar to the log backup message in the way it refers to a current database backup, but this time actually means that there is no current backup. :crazy:Thu, 28 Jun 2012 06:16:06 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxI got led astray by the error message. Since it said there was no current database backup, I thought that meant you couldn't do a differential backup at all (since there was no full backup to "diff" against). Moral - error messages don't always say what they mean...Thu, 28 Jun 2012 06:11:01 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxThanks for the question Steve. I was surprised to see the number of incorrect answers. I hardly ever administer DBs, mostly design and programming. Maybe the lack of certainty about the right answer helped in this case. It made me slow down and think it through. Too bad I don't do that in all cases. ;-)(Edited to fix non-spellcheckable typo.)Thu, 28 Jun 2012 05:47:58 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1322249-32-1.aspxGood question.However just because I could get away with a differential backup, the 'once bitten twice shy' part of me would always go for a full backup unless there was some overriding driver (time to backup?) pushing me to take a differential backup. It simplifies any future recovery of the database (full backup plus log file(s) vs full backup plus differential backup plus log files).Call me old fashioned if you like.Thu, 28 Jun 2012 04:35:34 GMTipounder