When SQL server restores the database, it prompts that the database is in use and cannot be operated.

The reason for this problem is that a user connects to the database to be restored, and the users here even include the users to be restored. The solution is to close all connections to the database to be restored.

① Switch the database to the master database first. Execution statement

Select * from master .. sysprocesses where dbid = db_id ('database name ')

② Then run the statement exec kill spid step by step (data in the result set in the previous step ),

The problem is solved. Haha

Today, when restoring the database, the system prompts "because the database is in use, it cannot obtain exclusive access to the database". No matter whether I restart the database or restart the computer, the problem cannot be solved, after several attempts, the problem was finally solved. The cause of the problem and the solution are written. If there is anything wrong, you are welcome to raise it.

Cause: When I restore the database, other users are using the database, so the above prompt will appear.

Solution:

1. Set the database to work in single-user mode.

Setting Method: Right-click the database to be restored, right-click the menu and choose "properties"> "option"> "status"> "Restrict Access"> "Single ". This is the menu command of SQLSERVER2005. For other versions, find them by yourself.

2. Use SQL statements to kill all processes that are using the database. I used to write SQL statements for this function when I was using an SQL server tool. I posted them for your reference:

Copy codeThe Code is as follows:
Declare @ dbname varchar (50)
Set @ dbname = 'database name'
Declare @ SQL varchar (50)
Declare cs_result cursor local for select 'Kill '+ cast (spid as varchar (50) from sys. sysprocesses where db_name (dbid) = @ dbname
Open cs_result
Fetch next from cs_result into @ SQL
While @ fetch_status = 0
Begin
Execute (@ SQL)
Fetch next from cs_result into @ SQL
End
Close cs_result
Deallocate cs_result

The SQL statement uses a cursor to cycle all processes that are using the database and uses the kill command to kill the processes.

3. Use SQL statements to disconnect all users and roll back all transactions. The specific SQL statements are as follows:

Copy codeThe Code is as follows:
Alter database [DATABASE name]
SET OFFLINE WITH ROLLBACK IMMEDIATE

Note: When using methods 2 and 3, do not run the command in the database to be restored. We recommend that you run the command in the master database.