Hi all,

I have a big problem (it's two actually).
I wanted to move system databases, so I shut down SQL server and copied sys DBs to another drive. (MSSQL 2008R2 Enterprise x64, Win Server 2008R2 x64)
I modified the startup command for new master location and admin mode:

-dc:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-c;-m;-T3608

I started SQL service, but it didn't start and cannot open tempdb files (read in Win event log). 
I had 4 fixed size tempdb files on a dedicated partition (4 GB each).
I thought I remove 2 piece to free up some space, so I made this in console:
(c:\Program Files\Microsoft SQL Server\100\Tools\Binn>SQLCMD.EXE" -S localhost -E)
ALTER DATABASE [tempdb]  REMOVE FILE [tempdev4]
ALTER DATABASE [tempdb]  REMOVE FILE [tempdev3]

Then I deleted the 2 useless files in Windows.

After this I started up SQL service:
SQLSERVR.EXE -m -c -T3608 -T3609

.... and tried to attach model DB:
sp_attach_db 'model','c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf','c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf'

...and tempDB was starting... and tried to reach tempdev3 and tempdev4!!

But those are already deleted!

I think it's a "deadlock" situation. "model" DB needs "tempDB", "tempDB" needs "model" DB...
It would be very important to fix this, it's a production server.

What should I do?
Thanks in advance!

Balint

Balint Fekete (kaktusztea@gmail.com) writes:
> I have a big problem (it's two actually). I wanted to move system
> databases, so I shut down SQL server and copied sys DBs to another
> drive. (MSSQL 2008R2 Enterprise x64, Win Server 2008R2 x64) I modified
> the startup command for new master location and admin mode: 
>... 
> I started SQL service, but it didn't start and cannot open tempdb files
> (read in Win event log). 
> I had 4 fixed size tempdb files on a dedicated partition (4 GB each).
> I thought I remove 2 piece to free up some space, so I made this in
> console: 
>...
> I think it's a "deadlock" situation. "model" DB needs "tempDB", "tempDB"
> needs "model" DB... It would be very important to fix this, it's a
> production server. 
 
I would recommend that you open a case with Microsoft to sort this out.
This is not a situation that is suitable to assist with in a forum. It 
appears that you have made a number of mistakes already, and while I 
can suggest that you could try this or that, it is not a situation that I 
have experience from, and more mistakes could lead you to a situation where
you cannot recover at all. With a dedicated support professional to assist 
you by phone or over mail, there is a higher degree of interaction.

What you should have done, I think, is to first have moved model, tempdb and 
msdb, following the procedures in
http://msdn.microsoft.com/en-us/library/ms345408%28v=sql.105%29.aspx. And 
once you have the other databases in place, you could also have moved 
master.

It is possible that you can get things working with the appropriate 
"ALTER DATABASE database_name MODIFY FILE" commands for model, tempdb and 
msdb.

But it may also be better restore from backup and start over.


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

Thanks for the answer.
Meanwhile I had luck: I made previously a copy about system mdf,ldf files.
I didn't think it will work, but I copied them back and the service started up, also created the missing tempdev files.