Setting single-user mode for restore operations

You might have to start an SQL Server instance in single-user mode during certain restore operations. For example, you might use single-user mode when you are restoring a damaged master database or a system database, or when you are changing server configuration options.

Before you begin

Restriction:

You cannot restore SQL databases that are in use. By placing SQL databases to be restored in single-user mode, you can avoid system attempts to restore those databases.
Microsoft Management Console (MMC) cannot connect to a SQL Server instance that is started in single-user mode. If you want to use MMC when the SQL Server instance is in single-user mode, you must use the command-line interface, tdpsqlc.exe, to restore the master database.

Procedure

To determine which users are using the databases, use the SQL stored procedure, SP_WHO.
To force users off the SQL database and set the SQL Server to single-user mode, issue this TRANSACT-SQL command.
```
ALTER DATABASE DBNAME SET SINGLE_USER
```
```
WITH ROLLBACK AFTER N SECONDS
```
To start the SQL Server in single-user mode, use the -m SQL SERVER startup option.
To return the database to multiple-user mode, issue this TRANSACT-SQL command.
```
ALTER DATABASE DBNAME SET MULTI_USER
```