1

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.

3
  • Added a link for Recovery
    – t_m
    Sep 9, 2014 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
    – Hannah Vernon
    Sep 9, 2014 at 14:09
  • Review your documentation you have on production that should have this information :)
    – user507
    Sep 9, 2014 at 15:48

2 Answers 2

1

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

5
  • Re-read the OP's question and you will realize your solution is not going to work...
    – user507
    Sep 9, 2014 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, 2014 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, 2014 at 15:54
  • @ShawnMelton .. Done
    – t_m
    Sep 9, 2014 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, 2014 at 16:09
0

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');
> SHUTDOWN WITH NOWAIT;
> GO

Now start SQL Server

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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