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

more ▼

asked Feb 13, 2015 at 02:53 PM in Default

avatar image

91 6

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Feb 13, 2015 at 07:08 PM

avatar image

3.5k 3 6

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.

Feb 16, 2015 at 11:10 AM Grant Fritchey ♦♦

Thanks, Grant! Great question.

Feb 17, 2015 at 01:16 PM DenisT

Thanks Grant and Denis For your support.

Mar 02, 2015 at 02:55 PM Chalapathi2205
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 13, 2015 at 02:53 PM

Seen: 109 times

Last Updated: Mar 02, 2015 at 02:55 PM

Copyright 2018 Redgate Software. Privacy Policy