oh no - I destroyed my tempdb table in MSSQL
Ok - so im not ashamed to admit it so yes I destroyed my MSSQL tempdb table. So what is this table?
Tempdb is a system database used by SQL Server to store temporary tables and temporary stored procedures, for sorting, subqueries, and aggregates with GROUP BY, ORDER BY, for cursors and so on. Tempdb database contains only temporary objects, so if you want to create a permanent object, do not create it in the tempdb database.
If you have done the same and need to restore your tempdb table then simple follow these steps:
Login to query analyzer and execute the below command:-
use master
go
Alter database tempdb modify file (name = tempdev, filename =
'drive:\folder\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename =
'drive:\folder\templog.ldf')
go
go
Alter database tempdb modify file (name = tempdev, filename =
'drive:\folder\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename =
'drive:\folder\templog.ldf')
go
where drive:\folder is the path the the folder you want the new tempdb file to go into.
Thank can also be used to relocate your files. You may well need to restart your SQL service after this.
Comments
[Add Comment]
tempdb is created automatically each time you restart the MSSQL service. Did you try restarting first?
# Posted By Alan McCollough
| 15/08/08 22:40
| Report abusive comment
I did but i also wanted to relocate the tempdb folder on a bigger drive.
# Posted By nick tong
| 16/08/08 13:14
| Report abusive comment
[Add Comment]
