urz


I am testing sql server 2005 in different disaster situations happened under 2000. I was reading a lot about no update on system tables, so:

sp_detach_db 'tempdb'

In sql 2000 i could issue something like the following command to recover:

use master

sp_configure 'allow updates', 1

reconfigure with override

go

insert sys.sysdatabases (name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version)

values ('tempdb', 2, 0x01, 0, 8, 1090520064, '2007-01-27 13:03:10.873', '1900-01-01 00:00:00.000', 0, '90', 'D:\beep\mssql\temp\tempdb.mdf', 611)

go

sp_configure 'allow updates', 0

reconfigure with override

go

As tempdb is hardcoded db id 2, I do not have a chance to recover without a master backup.

Of course, this situation can be recovered using a master backup, but it is not always available.

Thank you for your comments.




Re: How to recover from dropped tempdb?

Peter Byrne MSFT


How is it that you are detaching tempdb

1> sp_detach_db tempdb
2> go
Msg 7940, Level 16, State 1:
System databases master, model, msdb, and tempdb cannot be detached.
1>







Re: How to recover from dropped tempdb?

urz

Thanks for the fast response.

Yes, it is the normal behavior. But imagine the following situation:

The end-user tries to move the system databases to another disk. He reads the information on your site (http://support.microsoft.com/kb/224071/) and starts the server with /T3608. He executes the procedure step by step, moves msdb and model, forgets to read the remaining part and happy to move tempdb the same way.

This is the point, when you are getting involved. How to proceed






Re: How to recover from dropped tempdb?

Michael Hotek

Well, that script won'y work with SQL Server 2005. You can not allowed to make direct updates to system tables. You can't drop or detach a system database, so the only way of having an issue with tempdb is that it either becomes damaged while SQL Server is running in which case the SQL Server will go offline and fixing the issue is a matter of restarting SQL Server or you have an issue while starting up SQL Server in which case the SQL Server won't start and you would then need to go through the process documented in BOL for recreating tempdb.




Re: How to recover from dropped tempdb?

Peter Byrne MSFT

I have asked that the article be updated to remove SQL 2005 from the section that does the moves via detach. ALTER DATABASE should be used to change the file paths in these cases and there are detailed instructions in books online.




Re: How to recover from dropped tempdb?

urz

Thank you for your comment.

You _can_ detach the tempdb, if you start SQL Server 2005 with the /T3608 parameter [I have tried before posting the question]. From the point tempdb detached it cannot be re-attached, nor can be re-created using the way you mention, as the row with hardcoded database id "2" does not exists in the sysdatabases table.





Re: How to recover from dropped tempdb?

urz

Thank you.

For closing the issue can we say, that the only way to recover from this situation is attaching a clean master db and re-attach the databases and re-create all global settings (logins, endpoints, etc.) stored in master





Re: How to recover from dropped tempdb?

Peter Byrne MSFT

If you have a backup of master, you could restore that after putting in the clean master. Master is usually small enough that it should not be too big a burden to do full backups on a regular basis.

And if you had truly followed the article, the first step is

? Make a current backup of all databases, especially master, from their current location.

which should allow you to recover.