Workaround for SQL Server to prompt database when restoring database is in use and cannot be manipulated

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

① switch the database to the master database first. EXECUTE statement
SELECT * FROM Master. sysprocesses where dbid=db_id (' Database name ')
② then progressively runs the statement exec kill SPID (data from the previous result set),

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, finally resolved the problem. Now the cause of the problem and the solution is written out, there is no place to welcome everyone to ask.

Cause: Because when I restore the database, there are other users who are using the database, so the above prompt appears.

Workaround:

1, set the database to work in single-user mode. Setup 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 menu command for SQLSERVER2005, other versions please find it yourself.
2, the use of SQL statements to kill all the processes that are using the database, I used to do a SQL Server operation of the small tool has written this function of SQL, posted for your reference:

DECLARE @dbname varchar ( -)Set@dbname ='Database name'declare @sql varchar ( -) Declare cs_result cursor local for Select 'Kill'+cast (spid asvarchar -)) fromSys.sysprocesseswhereDb_name (dbid) =@dbname Open Cs_resultfetch next fromCs_result into @sql while@ @fetch_status =0begin Execute (@sql) fetch next fromCs_result into @sqlendclose cs_resultdeallocate cs_result

The SQL statement uses the cursor to loop through all the processes that are using the database and kills the process with the kill command.

3. Using SQL statements, disconnect all user links and roll back all transactions, with the following SQL statements:

ALTER database [DB name] SET OFFLINE with ROLLBACK IMMEDIATE

Note: When using method 2 and 3 o'clock, do not execute under the desired restored database, it is recommended to execute under the master database.

Workaround for SQL Server to prompt database when restoring database is in use and cannot be manipulated