SQL Server Fails to Start After ALTER DATABASE Sets Invalid tempdb File Path

Indicators

• SQL Server instance fails to start after an ALTER DATABASE command targeting tempdb
• SQL Server Error Log references an invalid or missing tempdb file path during startup
• Single-user mode (net start mssqlserver -m) fails with an error indicating tempdb could not be found
• Starting the service via net start with /f or trace flags allows sqlservr.exe to run but no client connections succeed
• SQLCMD and SSMS cannot connect when SQL Server is started via the service manager in any recovery mode

Likely causes

• Mistyped or incorrect file path specified in ALTER DATABASE tempdb MODIFY FILE command
• tempdb data or log file path changed to a non-existent or inaccessible directory
• ALTER DATABASE committed the bad path to master before the filesystem path was validated

Diagnostic steps

1. Attempt to start SQL Server in single-user mode: 'net start mssqlserver -m'. Observe whether the error confirms tempdb cannot be found, establishing the tempdb path as the root cause.
2. Review the SQL Server Error Log at the default path: C:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\Log\ERRORLOG. Identify the exact invalid path SQL Server is attempting to use for tempdb files.
3. If master is accessible independently (e.g., via a known-good instance), query the bad tempdb path: SELECT name, physical_name FROM sys.master_files WHERE database_id = 2; — note this will not work while the affected instance is down.
4. Open an elevated command prompt and navigate to the instance BINN directory: cd "C:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\Binn"
5. Start SQL Server as a foreground application in minimal configuration mode, recovering only master, using: sqlservr.exe -c -f -T3608. For a named instance append: -s InstanceName. Leave this window open and do not close it.
6. Open a second elevated command prompt and connect via SQLCMD: sqlcmd -S . -E (or sqlcmd -S .\InstanceName -E for a named instance). Wait for the '1>' prompt before proceeding.
7. Confirm the incorrect tempdb file paths currently registered in master: SELECT name, physical_name FROM sys.master_files WHERE database_id = 2; GO
8. Verify the intended correct path exists on the filesystem before executing the fix. Use xp_fileexist or confirm via Windows Explorer that the target directory is present and accessible to the SQL Server service account.

Resolution path

• Stop any attempts to start SQL Server through the Windows Service Manager to avoid repeated failed startups writing to the event log
• Open an elevated command prompt and navigate to the affected instance's BINN directory: cd "C:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\Binn"
• Start sqlservr.exe directly as a foreground application with minimal config flags: sqlservr.exe -c -f -T3608 (append -s InstanceName for a named instance). Leave this window running.
• Open a second elevated command prompt and connect with SQLCMD: sqlcmd -S . -E (or sqlcmd -S .\InstanceName -E). Confirm the '1>' prompt appears.
• Verify the incorrect tempdb paths registered in master: SELECT name, physical_name FROM sys.master_files WHERE database_id = 2; GO
• Confirm the correct target path exists on disk before executing the fix (use xp_fileexist or Windows Explorer).
• Issue the corrected ALTER DATABASE command — triple-check the path before executing: ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'C:\correct\path\tempdb.mdf'); GO — repeat for the log file (templog) if its path is also incorrect.
• Shut down the application-mode instance cleanly from SQLCMD: SHUTDOWN WITH NOWAIT; GO
• Start SQL Server normally via the Service Manager or: net start mssqlserver. Verify the instance starts successfully and tempdb initialises without errors.
• Confirm instance health by connecting via SSMS and reviewing the Error Log for a clean startup sequence.

Prevention

• Always verify the target directory exists and is accessible to the SQL Server service account before executing ALTER DATABASE MODIFY FILE on any system database
• Use xp_fileexist within a test query to validate the path prior to committing the ALTER DATABASE command
• Back up the master database immediately before making any changes to system database file locations
• Require peer review or a second engineer sign-off for all T-SQL changes targeting system databases (master, tempdb, model, msdb) in production
• Validate ALTER DATABASE syntax and paths in a non-production SQL Server instance before applying to production
• Script all system database modifications with clear comments documenting old and new paths, and retain a rollback script ready to execute if the instance fails to restart
• Consider creating a pre-change checklist that includes: path existence check, service account read/write permission on the target directory, and a master database backup

Tools

• sqlservr.exe (SQL Server application executable)
• SQLCMD
• SQL Server Management Studio (SSMS)
• net start / net stop (Windows service control)
• SQL Server Error Log
• Windows Command Prompt (elevated)
• xp_fileexist (T-SQL path validation)

References

• Broken tempdb location and can't recover – DBA Stack Exchange