tempDB in SQL

(not a SQL DBA) but.. what kind of data goes into the default system database "tempDB", i.e. if you didnt back it up, what is the risk, why would you ever need to restore it, or what data would you lose if you did not restore it? can you not just create a fresh tempDB if it doesnt really store any permanent data?

I know some of the system databases store important configuration data which I can then see the logic in backing them up, but is tempDB actually storing any specific useful data permanently?
LVL 4
pma111Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't backup tempDB. It's a database for temporary objects only and it's recreated every time MSSQL service starts.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
Tempdb contains just temporary data from all users and from the SQL engine itself. Tempdb damage should kill all existing sessions and the SQL server itself but SQL Server service restart will always recreate tempdb so this kind of damage is very unobvious.

There is no reason to backup/restore Tempdb in SQL Server.
Yashwant VishwakarmaSQL DBACommented:
TempDB:
It stores temporary objects like temporary tables, temporary stored procedures, temporary tables to store sorting etc.
dbid of temp database is 2.
 
Recovery model of temp database is SIMPLE.
 
We can’t take backup of tempdb.
It is created everytime when SQL Server restarted.
 
tempdev & templog are the logical file names of tempdb.
tempdb.mdf ( data file ) & templog.ldf are the physical files of tempdb.
 
select name, physical_name FROM sys.database_files;  query  can be use to see the physical file location of tempdb

Temp database as the name says it is used to do the temporary operations such as tables, stored procedures, cursors. Once the operation is over it will be cleaned & is minimally logged. TempDB is recreated everytime when SQL is started, so it is always have a clean copy of database hence backup & restore operations are not allowed in TempDB.
 
For more information : Read https://msdn.microsoft.com/en-us/ms186388         

http://www.experts-exchange.com/articles/18558/SQL-Server-Databases-Overview.html
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

pma111Author Commented:
why do you need to set page verifacation and to run DBCC CHECKDB over tempDB then if it is recreated every time SQL starts again?
Yashwant VishwakarmaSQL DBACommented:
DBCC CHECKDB uses tempdb for sorting purposes. We can run dbcc checkdb on tempdb but really we don't need it, it will not run properly as you can not have a snapshot of tempdb.

Read more:
https://msdn.microsoft.com/en-us/library/ms176064(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/aa937548(v=sql.80).aspx
http://blog.consultdba.com/2010/05/dbcc-checkdb-and-tempdb.html
http://blogs.extremeexperts.com/2010/05/24/checkdb-and-tempdb/
Vitor MontalvãoMSSQL Senior EngineerCommented:
why do you need to set page verifacation and to run DBCC CHECKDB over tempDB
You don't need to but since is possible some DBAs run it.
Talking by myself I don't do that but can accept who do it. No warm on that for sure.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.