Friday 17 August 2012

SQL Server–Start SQL Server instance in single-user mode

There are certain situations when you have to start SQL server in single-user mode from the start up. For example, you may want to restore Master database or recover other system database or to change server configuration options and that will require to start an Instance of SQL Server in single-user mode.

  • To start SQL Server in single-user mode is very simple. Please use MS-DOS change directory (cd) command to move to the correct (SQL Instance) directory before starting sqlservr.exe, as shown below

cd \Program Files\Microsoft SQL Server\MSSQL10.50.MSSQLSERVER\MSSQL\Binn

To start default instance enter the following command

sqlservr.exe –m

To start named instance of SQL Server enter the following command

sqlservr.exe - m -s <instancename>

Connect to SQL Server instance using SSMS or sqlcmd after running above command – Make sure to keep MS-Dos command window open. To stop single-user mode just press CTRL+C and it will prompt you to stop SQL Service.

  • Alternatively (easy) use SQL Server Configuration Manager and click on SQL Server Services. Right click on desired SQL Server Instance and go to properties. On the advance table enter param ‘-m;’ before existing params in the startup parameters. Press apply and then restart the SQL Server Instance service.

Make sure to remove added parameter and restart service to allow multiuser connection.

Note: Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.

Technorati Tags: ,,,,,,,,,,,,,,,,,