How to point to new location when the drive TempDB is located on is no longer available

How to point to new location when the drive TempDB is located on is no longer available

SQL Server requires the TempDB database to be accessible in order to start up.  If it cannot find the TempDB file, you will get an error when starting the service and it will not start.  The most likely scenario for this is when you have a drive, let’s say the D: drive, where TempDB is located on becomes corrupted or fails.   The issue is not that the file is unavailable, but the path specified isn’t, because SQL Server will always recreate the tempdb.mdf and templog.ldf files on startup.

First thing you’ll need to do is start SQL Server in “maintenance mode”.  Open a command prompt, and change directories to the BINN folder for your SQL server install.  For example, the default path for SQL Server 2008 R2 would be:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

Run this command at the command prompt:

sqlservr -c -f -T3608 -T4022

The -c “Indicates that an instance of SQL Server is started independently of the Windows Service Control Manager. This option is used when starting SQL Server from a command prompt, to shorten the amount of time it takes for SQL Server to start.

The –f “Starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting.”

The -Ttrace#” Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the server with nonstandard behavior. For more information, see Trace Flags (Transact-SQL).

Once you have this started up in the command prompt, open a new command prompt window and run the following command from the same “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn” location:

sqlcmd –E

You will be presented with the 1> prompt.  Here you can enter in SQL queries, and using the GO command will execute them.  We will do the following, hitting enter after each line (remember to replace {New Location} in the queries below with the file path you wish to use, i.e. the default “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data” for SQL Server 2008 R2):


USE master;
GO
GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = '{New Location}\tempdb.mdf');
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = {New Location}\templog.ldf');
GO



Once this is done, close out of this command prompt, and go to the other command prompt with sqlservr.exe running.  Hit CTRL+C to break it, and hit Y to accept shutting SQL Server down.  Now, go to SQL Server Configuration Manager and start the SQL Server service.  SQL Server should now recreate the TempDB files and start up normally again!