Cry How to...

Start SQL Server 2000 in Single User Mode

To start SQL Server in single user mode:

1. Start a command session (start > Run > "cmd" [Return]).
2. To ensure that SQL Server is not running type:
       net stop MSSQLServer
   This may say that this will also stop another service (typically SQLSERVERAGENT), in which case allow it to continue. It will also indicate if the service is already stopped.
3. To start SQL Server in single user mode type:
       sqlservr -m
   If this is not recognised then cd to 'C:\Program Files\Microsoft SQL Server\MSSQL\BINN' and try again.

Note:

• Single User mode will allow only a single user to connect to the database.
• While SQL Server is in single user mode you should still be able to connect from SQL Server Query Analyzer if you connect using the "sa" account.
• You can set a single database into single user mode using an alternative procedure described here: How to set a single database into Single User Mode - but this process cannot be used to set the MASTER database into single user mode, for that you must start SQL Server in single user mode.

To shutdown the server when it is in single user mode, simply type Control-C in the command window where sqlservr is running. It will then ask you if you wish to shutdown SQL Server.

You can then restart SQL Server by restarting the SQL Server process:

    net start MSSQLServer

be aware that this will not start any other processes which may have been stopped, such as SQLSERVERAGENT. So if any other processes where stopped then you will need to manually restart them (or reboot if that is easier - assuming the necessary services are set to start automatically).

These notes have been tested against SQL Server 2000 running under Windows 2000 and XP.