How to: Start an Instance of SQL Server (sqlservr.exe)

If the SQL Server Database Engine does not start, one troubleshooting step is to attempt to start the Database Engine from the command prompt. This topic describes how to start an instance of the Database Engine.

Note:
SQL Server should only be started from the command prompt for troubleshooting purposes. To start the SQL Server Database Engine for routine use, see How to: Start an Instance of SQL Server (SQL Server Configuration Manager).

 

Normal SQL Server startup is a complex activity, requiring correctly located SQL Server files, registry settings configured for SQL Server and Microsoft Windows services, and correct file and registry permissions for the domain account used by the SQL Server service. Therefore, a common troubleshooting step is to determine if SQL Server can start under ideal conditions by simplifying its environment. Usually the troubleshooting process starts with the question, “Can SQL Server start, using an account that is in the local Administrators group, and without using the Windows Service Control Manager?” A “yes” answer indicates that the problem may relate to the Service Control Manager, or to the permissions assigned to the account that the SQL Server service is attempting to use. A “no” answer may reveal an error message that was previously not displayed through the Service Control Manager, and may indicate problems with the SQL Server files or registry settings.

Caution:

Do not run SQL Server from the command line for any purpose other than to test whether SQL Server can be started. Maintenance actions performed after SQL Server has been started from the command line could cause failures later. For example, if you are logged in as an administrator, and create a new database or change the location of a data file, SQL Server may be unable to access it when running later under the normal account for the SQL Server service.

 

Consider the following limitations when running SQL Server from the command line:

• Minimizing the command prompt window will cause Windows to remove nearly all resources from SQL Server. This is normal, because SQL Server is not designed to operate in this manner.
• SQL Server runs in the security context of the user, not the security context of the account assigned to run SQL Server during setup.
• All system messages appear in the window used to start an instance of SQL Server.
• You cannot pause, stop, or resume an instance of SQL Server as a Microsoft Windows service using SQL Server Management Studio, SQL Server Configuration Manager, the Services application in Control Panel, or any net commands (for example, net start, net pause, net continue, and net stop).
• You must shut down the instance of SQL Server before logging off Windows.
• Any command prompt options that you type take precedence over the default command-prompt options written to the Windows registry by SQL Server Setup.
• SQL Server Configuration Manager and SQL Server Management Studio show the service as stopped.

 Starting the Correct Instance of SQL Server

By default, sqlservr.exe is located at C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn. If a second instance of SQL Server is installed, a second copy of sqlservr.exe is located in a directory such as C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\binn. You can start one instance of SQL Server by using sqlservr.exe from a different instance, but SQL Server will start the version of the incorrect instance as well, including service packs, which may lead to unexpected results. To avoid this, use the MS-DOS change directory (cd) command to move to the correct directory before starting sqlservr.exe, as shown in the following example.

cd \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

 To start the default instance of SQL Server from a command prompt

1. From a command prompt, enter the following command:
   sqlservr.exe

    

 To start a named instance of SQL Server from a command prompt

1. From a command prompt, enter the following command and replace <instancename> with the name of the instance you want to start:
   sqlservr.exe -s <instancename>

    

 To start the default instance of SQL Server in single-user mode from a command prompt

1. From a command prompt, enter the following command:
   sqlservr.exe -m

   Single-user mode can be useful for performing emergency maintenance when you do not want other users to connect to SQL Server, but any user can become the single user, including the SQL Server Agent service.

    

 To start a named instance of SQL Server in single-user mode from a command prompt

1. From a command prompt, enter the following command:
   sqlservr.exe -m -s <instancename>

    

 To start the default instance of SQL Server with minimal configuration

1. From a command prompt, enter the following command:
   sqlservr.exe -f

    

 To start a named instance of SQL Server with minimal configuration

1. From a command prompt, enter the following command:
   sqlservr.exe -f -s <instancename>