Recently we migrated one of our client’s SQL Server instances to a new server. During the migration we had moved the storage to the new server so the old server didn’t have some of the disk drives that were previously mounted on it.
After the migration the client requested that we bring up the old SQL Server instance for some testing. This was not requested by the client during the planning phase so we were not prepared for this. Unfortunately, the disk drive that was used to store the data files for TEMPDB was now mounted on the new server. So I could not start the SQL Server instance because of the following errors.
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'C:Datatempdbdatatempdb.mdf'.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file C:Datatempdbdatatempdb.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "C:Datatempdbdatatempdb.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
Error: 1802, Severity: 16, State: 4.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Error: 5123, Severity: 16, State: 1.
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'C:Datatempdbdatatempdb.mdf'.
Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.So we could not start the TEMPDB and there was no option to mount the drive back.
There is a ‘-f’ startup option that you can use to start SQL Server with minimal configuration. This does put the SQL Server instance in single user mode. So starting SQL Server using the command line was the way to go.
Following are the steps needed to add a new file to TEMPDB and then restart SQL Server.
1) Open command prompt window #1 as ADMIN and go to the BINN directory where SQL Server is installed. This is typically in
C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinnOR
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn2) Then execute command like
Sqlservr.exe /f /c 
3) Then open one more command window #2 and if this is a default instance then open SQLCMD using the following command
SQLCMD –S localhost –E4) This will open a SQL command prompt there where you can type the following commands
1> USE MASTER
2> GO
3> ALTER DATABASE tempdb MODIFY FILE
4> (NAME = tempdev, FILENAME = 'C:NEWPATHdatatempdb.mdf')
5> GO
6> quit
5) 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
6) Now start the SQL Server instance from configuration manager. You should see the new data file created and SQL Server started.