Starting SQL Service with minimal configuration for issue with Tempdb location
If a SQL instance starting up as a service cannot find the storage device/ Disk location where the tempdb files are stored then the instance will fail to startup.
Since the service cannot be started, the tempdb location can not be reconfigured from SSMS in a normal way. The only way to Reconfigure TempDb File locations is through command-prompt window by Connecting to SQLCMD with minimal configuration.
Steps involved in this activity are:
1.Connecting to SQLCMD with minimal configuration.
2.Reconfigure the tempdb location
3.Stop the command-prompt instance
4.start the service from SQL Configuration manager.
To start a SQL instance in a command-prompt window, cd to the instance’s Binn directory (eg.cd ‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQL_INSTANCEMSSQLBinn’), and enter the following command:
sqlcmd -S "MY_SERVERSQL_INSTANCE"
1> USE master
2> go
Changed database context to 'master'.
1> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:temptempdb.mdf')
2> go
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
1> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:temptemplog.ldf')
2> go
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
1> quit