Starting SQL Server - Minimal Configuration using the –f switch

I noticed something today that hadn’t before…when you start SQL Server in Single User Mode using the –f switch, tempdb gets recreated back to where the model database is destined.

First I run the following:

You can see that I have four tempdb files on the F:\ drive. Now I start SQL Server in the minimal mode with –f:

Stop and start the services and you see that tempdb goes back to its original locale:

Restarting with the –f switch taken out, and tempdb is once again recreated back in the F:\ location with four tempdb files.

Trying the same with the –m option (single-user) and you don’t find the same behavior. The tempdb database does not get moved back to the default location with the original default configuration; all of the files stay in the same location.

Read more on starting SQL Server at the following links:

http://msdn.microsoft.com/en-us/library/ms188236.aspx
http://msdn.microsoft.com/en-us/library/ms180965.aspx
http://msdn.microsoft.com/en-us/library/ms191193.aspx

Thanks for reading,
Lee

------------------------