Moving the tempdb to unknown location

  • Article

One of my friends called me for a problem at a customer site. The problem simple is that the customer wanted to move the tempdb in SQL Server 2005 to a new location but because of a typo the new path of the file is pointing to a folder not a file. Something like this "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"

The alter statement would be something like this

alter database tempdb

modify file (name=tempdev, fileName='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\')

The result of this statement would be

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

So next time SQL Server tries to access the tempdb it will fail and the problem comes worse if you restarted the SQL Server, the Server won't start and this message will be logged in the Application Event log

FCB::Open: Operating system error 3(error not found) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'. Diagnose and correct the operating system error, and retry the operation.

So what would be the solution of something like this?

I've tried this scenario and worked, I thought it would be better if I published it so if someone faced this situation (although, it's very rare situation) it would help.

  • First we need to start SQL Server with minimum configuration in console mode; go the SQL Server binary folder (commonly in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn)

  • Open a console window and run this command

    sqlservr.exe –c –f –m

So this starts SQL Server in console mode (not a service) and with minimum configuration and also with single user mode. You should see some messages like you see when you open the error log file

  • Open another console window and run this command

    Sqlcmd /A

    This will connect to the default instance on the machine using windows authentication though Dedicated Administrator Connection (DAC)

    After logging you can alter the tempdb to add the path of the correct file; like this.

alter database tempdb modify file (name=tempdev, fileName='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ tempdb.mdf')

Now return back to the first console window where you started the SQL Server and press Ctrl+C this will shutdown the server

Start SQL Server normally and the problem is now solved J