asked
Secret of TEMPDB
why we can't take backup of tempdb? This question asked one senior interviewer Stupidly, i said answer to him, taking the temp db backup is not possible, why because the temp db is always recreated while restarting your sqlserver. if at all you try to take a backup of temp db it will give an error: Backup and restore operations are not allowed on database tempdb., but he was not happy with my answer, really i didn't understand why he was not satisfy with my answer. Any one give the answer this. Chalapathi DBA
I don't know if this is the right answer but if you think about how the logging is done in tempdb. The transactions log enough information only to roll back (undo) but not to recover (redo). Now, if you have a backup, you have to be able to restore it. Otherwise why do you back up your databases if you cannot restore them? Restore goes thought 3 phases: 1. Analysis 2. Redo 3. Undo Since tempdb's logging is ignoring REDO and logging only UNDO, the restore recovery cannot be completed. Hence, the database cannot be restored and there is no point of backing up a database that cannot be restored. Perhaps the interviewer was looking for an explanation to see why the tempdb database cannot be backed up. I don't know! :) Let's see what the gurus will say about this. I'd explain this question in these terms.
I think you've largely nailed it. I would have stepped out to an even lighter, more logical statement and talked about the fact that since everything there is temporary in nature and tied to session id's that won't ever recover, attempting to restore it is a waste of time and effort, so why back up? Your answer is better.
18 People are following this question.
