Starting a MSSQL Instance in a Command-Prompt Window

Starting a SQL Instance in a Command-Prompt Window

If the SQL instance fails to start as a service, it can be started in a command-prompt window. Generally, this is only done for troubleshooting purposes. (See http://msdn.microsoft.com/en-us/library/ms180965(v=SQL.100).aspx) For example, if a SQL instance starting up as a service cannot find the storage device that the tempdb files are to be stored on, the instance will fail startup. Since the service cannot be started, its tempdb location also cannot be reconfigured in the normal way. In this case, the solution is to run the instance in a command-prompt window, reconfigure the tempdb location, stop the command-prompt instance, and start it again, running as a service.To start a SQL instance in a command-prompt window, cd to the instance's Binn directory (eg. 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL_INSTANCE\MSSQL\Binn'), and enter the following command: sqlservr -f -s "SQL_INSTANCE" This command runs the SQL instance named "SQL_INSTANCE" inside the command-prompt window itself, with a minimal configuration. A minimal configuration limits instance execution to a single user. To reconfigure the tempdb file locations, open another command-prompt window and use one of the SQLCMD.EXE methods shown above. When finished, return to the command-prompt window in which SQL is running, type Ctrl-C, and confirm the shutdown request. Afterward, run the SQL instance as a service.