solution that prompts the database to be in use when SQL Server restores the database _mssql

The reason for this problem is that a user is connected to a database that is currently being restored, and the user here even includes the user who is currently being restored. The workaround is to close all connections to the database you want to restore.

A method recommended by the cloud-dwelling community: Restart the SQL Server service within the service.

Description of the problem: after the SQL Server database backup is restored, the word "restricted access" appears after the database name

Problem two description: When restoring to a SQL Server database, prompt: System.Data.SqlClient.SqlError: Cannot obtain exclusive access to a database because the database is in use. (MICROSOFT.SQLSERVER.SMO). This problem occurs because another user is using the database when the database is restored. Restoring a database requires that the database work in Single-user mode. Typically, the DBA does not allow other users to connect to the database while it is operating.

Problem One solution:

Right-click the database-> properties-> option-> status-> restricted access-> Select multiple-> OK.

Problem Two solutions:

Method one (most convenient): Right-click the database-> properties-> option-> status-> restricted access-> select Single-> OK. and then restore.
Method two (most direct): Disconnect the database
Method III (Most violent): Logout/Restart the database server
Method four (most troublesome): Write code to modify the database related properties, although the trouble, sometimes still need to use, then use the time to study.

① first switch the database to the master database. EXECUTE statement

SELECT * FROM Master. sysprocesses where dbid=db_id (' Database name ')

② then runs the statement exec kill SPID (data in the result set in the previous step).

The problem is solved. Ha ha

Today, when restoring the database, the prompt "because the database is in use, so cannot obtain exclusive access to the database", whether I restart the database, or restart the computer, can not solve the problem, after many attempts to finally solve the problem. The cause of the problem and the solution are written out, there is no place to welcome everyone to come forward.

Cause: Because when I restore the database, there are other users who are using the database, so the above prompts will appear.

Workaround:

1, set up the database to work in Single-user mode.

Set method: Right-click on the database you want to restore, select Properties-> Options-> Status-> "Restrict Access"-> "single" On the right-click menu command. This is the SQLSERVER2005 menu command, and other versions please find them yourself.

2, using the SQL statement, killing all the processes that are using the database, they used to do a SQL Server operation gadgets have written this function of SQL, posted for your reference:

Copy Code code 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 (m) from sys.sysprocesses where db_name (dbid) = @dbn Ame
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 loops through all the processes that are using the database and kills the process with the kill command.

3, use the SQL statement, disconnect all user links, and roll back all transactions, the specific SQL statements are as follows:

Copy Code code as follows:

ALTER database [DB name]
SET OFFLINE with ROLLBACK IMMEDIATE

Note: Do not execute under the required restored database when using methods 2 and 3 o'clock, and it is recommended that you perform it under the master database.