In "Shutting Down SQL Server," I showed you seven ways to shut down SQL Server. Naturally, you also have multiple options for starting SQL Server. Here are seven ways to start a SQL Server system.
Related: Avoid Auto-Growing Tempdb When SQL Server Starts
Enterprise Manager
The most obvious way to start SQL Server is by using Enterprise Manager. Select the SQL Server system you want to start, then select Start from the pop-up menu. You can start SQL Server on both the local system and remote servers as long as you have rights to start services on the remote systems.
SQL Server Service Manager
The SQL Server Service Manager applet can also start both local and remote servers as long as the remote server is registered in Enterprise Manager and you have remote-service rights. Double-click the SQL Server Service Manager icon in the system tray. Then, from the Server drop-down box, select the name of the system you want to start. Select the SQL Server service, and click Start.
Windows Services
You can also start SQL Server by using the Windows Services applet. Open the applet by using the Windows Start, Programs, Administrative Tools, Services menu. Then, double-click the MSSQLServer service, and click Start to start the default instance. If you want to start a SQL Server named instance, look for the service called MSSQL$instancename.
The Net Start Command
The previous start methods all use the Windows GUI to start SQL Server, but you can also use the Net Start command. Type net start mssqlserver at the command prompt. To start a named instance, type net start MSSQL$instancename.
The Sqlservr.exe Command
You use the sqlservr.exe command to start SQL Server when you want to enable debugging of extended stored procedures. To start an instance of SQL Server, you must run the sqlservr.exe program from the \binn directory, where the SQL Server binary files are stored by default. Note that the SQL Server Service Manager and Enterprise Manager will show the service as stopped after you've run sqlservr.exe, but the server will support connections. When you start SQL Server from the command line, it's not running as a service and will shut down when you log off the server.
SQL-DMO
SQL Distributed Management Objects (SQL-DMO) can perform virtually every SQL Server management function, including starting and stopping the server. To use SQL-DMO to start SQL Server, first create an instance of the SQLServer or SQLServer2 object, then execute the Start method.
WMI
The Windows Management Instrumentation (WMI) Provider invokes the SQL-DMO objects on that system. To use the WMI Provider to start SQL Server, first instantiate the MSSQL_SQLServer object, then invoke the Start method. You install the WMI Provider for SQL Server from the \x86\other\wmi directory on the SQL Server installation CD-ROM.
Learn more: Blocking SQL Server Agent Stops and Starts
