Database Recovery from Single User Mode-SQL Server

Here in this blog post, we will discuss why the database of database server goes to sigle user mode and how to recover it. We will also discuss what does happens when a database of database server goes to single user mode in sql server.

Database Recovery from Single User Mode-SQL Server

Getting Started

Generally when user tries to restore the database and the database restoration gets failed because of any reason then database goes to single user mode in sql server.

If you try to access or expand the database in Object Explorer then you will face error like given image.

To recover the database from single user mode in sql server, use the below code then refresh the Object Explorer.

 USE master;  
 GO  
 ALTER DATABASE database_name  
 SET MULTI_USER;  
 GO  

If the database does not recover then restart the SQL Service and logout from Database Engine. To restart the SQL service follow the below steps.

Restart an instance of the Database Engine

1. In Object Explorer, connect to the instance of the Database Engine, right-click the instance of the Database Engine you want to start, and then select Start, Stop, Pause, Resume, or Restart.
2. Or, in Registered Servers, right-click the instance of the Database Engine you want to start, point to Service Control, and then select Restart.
3. If the User Account Control dialog box appears, select Yes.
4. When prompted if you want to act, select Yes.

Set Database to Single User in SQL Server

A database also can be set to single user mode in SQL server for maintenance by using Transact-SQL(SQL) code or SQL Server Management Studio (SSMS) to access one user at a time.

using Transact-SQL(TSQL) Codes

 USE master;  
 GO  
 ALTER DATABASE database_name  
 SET SINGLE_USER  
 WITH ROLLBACK IMMEDIATE;  
 GO  

SQL Server Management Studio (SSMS)

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Right-click the database to change, and then select Properties.
3. In the Database Properties dialog box, select the Options page.
4. From the Restrict Access option, select Single.
5. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, select Yes.

What will heppen when DB to Single User in SQL Server

Below two things will be happening when database of database server goes to single user mode in sql server. those are :

1. If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.
2. The database remains in single-user mode even after the user that set the option is disconnected. At that point, a different user, but only one, can connect to the database.