0

I've just made a boo-boo in my personal SQL instance. I wanted to move tempdb files to C:\tempdb.mdf and C:\templog.ldf respectively. The alter database ...modify file... command ran successfully. However, what I didn't realise was that I cannot create a file in C:\ , only folders can be created there. So after running the modify file command and restarting the service, the new files were not created i.e. C:\tempdb.mdf and C:\templog.ldf.

When I tried to get properties of the tempdb database to see where the files were pointing to, I found I could not get the properties nor any longer connect to the database instance. So eventhough I can restart the services in Config Manager, I am unable to connect to the instance either using SSMS or SQLCMD. I get the error that the file cannot be found.

Apart from re-installing the instance (as I don't have a backup of the master database), is there any other way I can fix this issue please?

Thank you.

1 Answer 1

1

I believe you should be able to startup the SQL Server instance in minimal configuration mode and connect to it via the command prompt with SQLCMD, as discussed in this article: How to Start SQL Server if You Lost TEMPDB Data files. Note this will put the server in single user mode.

After you connect to it, you should be able to execute ALTER DATABASE tempdb MODIFY FILE and modify the file locations to an appropriate location. Then shutdown and restart the instance.

Steps from the aforementioned article for reference:

  1. Open command prompt window #1 as ADMIN and go to the BINN directory where SQL Server is installed.
  1. Then execute command Sqlservr.exe /f /c
  1. Then open one more command window #2 and if this is a default instance then open SQLCMD using the following command SQLCMD –S localhost –E
  1. Execute the ALTER DATABASE command: USE MASTER GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb, FILENAME = 'C:\NEWPATH\tempdb.mdf') GO
  1. Now go back to Command window #1 and hit CTRL C. It will ask if you want to stop the instance. Y/N. Enter Y
  1. Now start the SQL Server instance from configuration manager. You should see the new data file created and SQL Server started.
1
  • Btw might be best to start backing up master (and really all databases in general).
    – J.D.
    Jan 20, 2022 at 4:09

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.