x

Temp Database backup

Hi Team,

What is the reason that we cannot take the backup of Temp DB database in SQL Server ?

Thanks Basit
more ▼

asked Sep 22, 2011 at 01:32 PM in Default

basit 1 gravatar image

basit 1
499 51 63 85

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

2 answers: sort oldest
tempdb is recreated each time SQL Server Services is restarted. There is no need to back it up. Additional things you should be considering about tempdb is to have it sized properly and depending on contention, have an adequate number of equal size data files to have a proper number of PFS, GAM, and SGAM pages to handle latch contention. Also tempdb should be placed on a fast disk subsystem and typically isolated away from other user and system databases. Since tempdb is recreated when SQL Server Services is restarted, why would you ever need a backup of the database, there is nothing to recover. :)
more ▼

answered Sep 22, 2011 at 01:33 PM

Tim gravatar image

Tim
36.4k 38 41 139

is that the only reason that temp db didn't backup as it contains the temporary object
Sep 24, 2011 at 04:10 AM basit 1
Several reasons, 1) Tempdb only contains temporary data so you wouldn't want to back it up. 2) Tempdb is recreated each time SQL Server Service is restarted so why would you want to back it up. 3) Most importantly backup up tempdb isn't allowed. Check out the comments on this link
Sep 24, 2011 at 05:24 AM Tim
"TempDB only contains temporary data". If only... It's what it should do, anyway. The number of times I've seen model filled with stuff that's only needed in tempdb that should instead be created by suitable coding...
Sep 24, 2011 at 06:21 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
tempdb only contains temporary objects, either user created when you create temporary tables or system created by sorting, hashing, or version store. There is no need for backup, and you should never create any "real" objects in tempdb, because they will be removed next time the server is restarted. SQL server creates a new tempdb every time.
more ▼

answered Sep 22, 2011 at 01:37 PM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x241
x7

asked: Sep 22, 2011 at 01:32 PM

Seen: 1774 times

Last Updated: Sep 22, 2011 at 01:33 PM