|
Hi Team, What is the reason that we cannot take the backup of Temp DB database in SQL Server ? Thanks Basit
(comments are locked)
|
|
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. :) is that the only reason that temp db didn't backup as it contains the temporary object
Sep 24 '11 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 '11 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
Sep 24 '11 at 06:21 AM
ThomasRushton ♦
(comments are locked)
|
|
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.
(comments are locked)
|
