June 27, 2012 at 8:58 pm
Comments posted to this topic are about the item Restart Log Backups
June 28, 2012 at 12:26 am
Got burned by this one. Very interesting question, good job Steve.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 28, 2012 at 12:37 am
too quick on the trigger - owned.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 28, 2012 at 12:59 am
Happy to be enlightened about that myth. It definitely worth two points.
June 28, 2012 at 1:57 am
Agggghhhhh 😛
Good question and a very good learning point, thanks
June 28, 2012 at 2:58 am
Good Question, explanation scripts were good 🙂
June 28, 2012 at 4:35 am
Good 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.
June 28, 2012 at 5:47 am
Thanks 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.)
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
June 28, 2012 at 6:11 am
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...
June 28, 2012 at 6:16 am
Duncan Pryde (6/28/2012)
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...
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 1
Cannot 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 1
BACKUP 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:
June 28, 2012 at 6:24 am
I'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.
June 28, 2012 at 6:43 am
Ouch!!! Got me!!! - Excellent question, learned something new.
June 28, 2012 at 6:55 am
I 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.
June 28, 2012 at 7:23 am
Got 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 (http://www.petri.co.il/sql-server-recovery-models.htm):
...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.
Great question, Steve!
Rich
June 28, 2012 at 7:26 am
Good question, got this wrong though. Nice to have a myth busted.
M&M
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply