I'm restoring to an alternate server in a DR drill and have restored master but can't restart the instance, even with /f and /m switches. The log is indicating tempdb can't be created, probably looking for the paths from the source instance. How can I determine which path it's trying to create tempdb's DB files in?

I was able to restore the master backup under an alternate name on a different instance so I can browse the system tables. Is there a spot where I can look for them?

I have to go with the assumption that access to the source server is lost so I can only rely on the backups to determine the correct configuration.

  • Added a link for Recovery – t_m Sep 9 '14 at 13:58
  • Check the SQL Server log for error messages. It'll tell you the location for tempdb. Typically, this would be something like C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG – Max Vernon Sep 9 '14 at 14:09
  • Review your documentation you have on production that should have this information :) – user507 Sep 9 '14 at 15:48

Refer to Instance will not start

There is a section called: TempDB location does not exist, you should be able to see the location in the SQL server log

  • Re-read the OP's question and you will realize your solution is not going to work... – user507 Sep 9 '14 at 15:50
  • @ShawnM .. It was clarified after my initial post. Therefore I have modified answer to include link Instance will not start and the OP can go from there. – t_m Sep 9 '14 at 15:51
  • Then you should remove the first portion of your answer that does not apply. You can also easily summarize Gail's fix for tempdb issue in a few statements and reference the article for more details. – user507 Sep 9 '14 at 15:54
  • @ShawnMelton .. Done – t_m Sep 9 '14 at 15:55
  • Thanks @t_m. Trace flag 3608 did the trick to allow me to read the sys.master_files table. – MattyZDBA Sep 9 '14 at 16:09

Start SQL Server in minimal config mode, master only configuration, from a command prompt:

[path to this instance]\Binn\sqlservr.exe -c -f -T3608

(Add -s InstanceName incase it is named instance.)

Now, in a different command prompt, connect using SQLCMD:

> sqlcmd -E

now change the path of the tempdb and shutdown

> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, filename = 'E:\Data\tempdb.mdf');
> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, filename = 'E:\Data\tempdb.mdf');
> GO

Now start SQL Server

  • Re-read the OP's question and you will realize your solution is not going to work... – user507 Sep 9 '14 at 15:49
  • @ShawnMelton , yes read it again, it was modified so I have modified my answer.Thanks :) – vijayp Sep 10 '14 at 4:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.