June 14, 2006 at 7:58 pm
I made a stupid mistake --- I have detached my tempdb database. Anyway I can get it back?????
June 15, 2006 at 12:55 am
How you managed to detach your tempdb? As sql server doesn’t allow to detach any of the system db's.
------------
Prakash Sawant
http://psawant.blogspot.com
June 15, 2006 at 1:03 am
hi, not sure what your exact problem is, but this might help :-
http://qa.sqlservercentral.com/columnists/sjones/atemporarymove.asp
it explains how to move tempdb.
June 15, 2006 at 4:49 am
Hi,
I didn't got how you detached the tempdb. Its just an idea please take backup of temp db that is existing on different server. Try to restore the database with tempdb name.
June 15, 2006 at 6:12 am
Did you really detach it? Or did you just accidentally delete the file?
Have you tried to stop and restart SQL Server (stopping and starting SQL Server rebuilds the TEMPDB from scratch).
-SQLBill
June 15, 2006 at 10:45 am
I have detached it with the flag 3608(stupid me). Stopping and restarting didn't help. Then I spent the whole night reinstalling sql-server and restored all dbs....
I guess it's a hard way of learning the fact that you can detach some of the system dbs but not master and tempdb....
June 16, 2006 at 4:31 am
This can be fixed. You need to start SQL with the -T3608 and -f flags, and do the work in Query Analyzer.
1) Start SQL and QA as above
2) Run sp_attach tempdb (see BOL for full syntax).
3) Run UPDATE master..sysdatabases set dbid = 2 where name = 'tempdb'
4) Remove the -T3608 and -f flags and restart SQL.
You can use the same process if you ever detach model. In this case set dbid to 3. I've never tried detaching master...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 16, 2006 at 6:42 am
This is some thing very usefull information which i was not aware of.
Thanks for the info.
------------
Prakash Sawant
http://psawant.blogspot.com
June 16, 2006 at 11:00 am
I wish I have seen this earlier.... I kind of tried to update the database ID of newly attached tempdb to 2 but then hesitated if it will still leave something hanging, so I have decided to reinstall everything...
Thanks for the info though!
June 19, 2006 at 3:24 am
If you have access to a 'Crash & Burn' server it is worth trying out some of these tricks.
Recently I was asked to get SQL working on a server where the server name and drive letters had been changed after the SQL install. (Just shows what can happen when DBAs are not allowed to support all the SQL boxes in an organisation...) The startup parameters wanted master .mdf on E: and .ldf on F: but the drives were now labelled D: and E:. The original place for model, msdb, tempdb and user databases was also E: and F:. It took a while to fix this server, but it is now running OK using D: and E: drives.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply