Backups

Question

Post reply

Backups

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

Interesting. A slightly unfair question I think since I know that tempdb cannot be backed up.

Tested on 2008 R2 RTM install and what do you know...

Msg 3147, Level 16, State 3, Line 1

Backup and restore operations are not allowed on database tempdb.

Never mind, eh. A better QOTD tomorrow please!

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Poor wording.. TempDB selected and got it wrong :angry:

51% selected tempDB as answer... I was also sure that we can't take 'tempDB' backup.

Still learn something new today...

Thanks

Thomas Abraham SSChampion Points: 10761 More actions · Answer 3

I guess I was not yet awake enough to have gone for tempdb. I also was lucky enough to come across:

http://www.sqlservercentral.com/Forums/Topic846325-146-1.aspx

Regardless of wording, learned something. Thanks for the question.

(edited to correct HTML tags.)

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

sestell1 SSChampion Points: 10230 More actions · Answer 4

Ugh, thought this was an easy one... should have done more research first. I'd forgotten the master database doesn't allow partial restores.

It's interesting that it will still let you set the recovery model to full or bulk-logged, but still treats it as if it were set to simple:

"For backwards compatibility with earlier versions of Microsoft SQL Server, the recovery model of master can be set to FULL or BULK_LOGGED. However, BACKUP LOG is not supported for master. Therefore, even if the recovery model of master is changed to full or bulk-logged, the database continues to operate as if it were using the simple recovery model.

"

http://msdn.microsoft.com/en-us/library/ms365937(v=sql.105).aspx

coldfusion244 SSC Veteran Points: 200 More actions · Answer 5

I was torn as well, I knew master couldn't be but tempdb can't be backed up at all so that makes it very difficult to get a differential... Very poorly chosen and worded question as there are two correct answers.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 6

It would be one thing if there had been check boxes. But since there was a radio button, and since I knew you couldn't back up the tempdb, it seemed clear that you had to check tempdb. Question is definitely flawed.

Dalton Moore SSC Eights! Points: 879 More actions · Answer 7

I considered master and then thought it could not be a trick... fell for the tempdb...

mtassin SSC-Insane Points: 23099 More actions · Answer 8

Koen Verbeeck (5/29/2012)
Dammit, want my point! 😛

Me too, though I learned about Master not allowing differential backups, Tempdb not allowing any was the obvious answer.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 9

I fell victim as well and answered tempdb, but I learned something.

I don't feel so bad seeing that lots of folks answered the same.

Converting oxygen into carbon dioxide, since 1955.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 10

mohammed moinudheen (5/28/2012)
I got this wrong as I selected the most evident answer 🙂

Same here. 🙂

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Revenant SSC-Forever Points: 42467 More actions · Answer 11

I guessed but got it right... Thanks for the question!

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 12

interesting question and discussion this morning - cheers

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Hugo Kornelis (5/29/2012)
But the phrasing of the question is poor. I assume the intention was to ask which system database can be backed up full but not differential, but the phrasing only included the inability of differential backups. And that means that tempdb is a valid answer as well, since tempdb doesn't allow backups of any kind.
Steve, if you're reading this - my suggestion is to change the wording of the question to "Which of the following system databases does allow full backups, bot does not allow differential backups."

Agree with Hugo that the question is not well put. I guess I was one of the lucky ones who knew about master. If I hadn't known about master I would have picked tempdb, because of course tempdb is also a right answer, I would not have done any research to find another correct answer. as it was, I found myself wondering whether the question's author didn't know about master so that his answer would be tempdb, because surely if he did know about it we would have had tick boxes not radio buttons, so that we could select two of the four answers, and almost picked tempdb myself for that reason, but then decided that the mistake of thinking only of the less well known answer and disregarding the well known one was more likely than ignorance as a reason for the misformulation of the question.

Tom

EL Jerry SSCertifiable Points: 7063 More actions · Answer 14

Good question, better discussion. I agree both the question and the answer would've been better worded, so let me add my $0.02 here.

The question says "Which of the following system databases does not allow differential backups." To me, this meant "there is a system database that allows other kinds of backups, except differential, which one is it?". Upon researching I found TEMPDB does not allow any kind of backup, so TEMPDB would not be the answer. Further research took me here:

http://my.safaribooksonline.com/book/-/9780735670273/ivdot-microsoft-sql-server-2012-optimization-maintenance-and-recovery/id3125960

which led me to the right answer (first TIP box).

I also agree that the reference could be improved by using a more updated source.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 15

The question had been corrected to note diff, but not full. I'll award back points as this was a tricky question with poor wording.