You might get the error related to "Exclusive access could not be obtained because the database is in use" so, in this article we will gain over this error.
Introduction
While you are restoring the database from the
backup you might get the error
"Exclusive access could not be obtained
because the database is in use". This article will help to overcome this
error at the time of restore the database.
Why you are getting this error?
This error occurs only when the connection to the database is open and you are trying to restore the database.
Way to resolve the error.
You have to close all active connections.
Way to resolve the error.
You have to select the database to connect to database engine.
Use [Master] --We have used the default database
available in all sql server
Go
--Now declare the variable for the database name
DECLARE @DatabaseName VARCHAR(50)
SET @DatabaseName = ‘DBName’ -- Set the database name
--Declare the table to save the SID which are connected to the
Database
DECLARE @SIDList TABLE
(
ID INT IDENTITY(1,1),
SID INT
)
--Now, select the SPIDs and insert it in the table from where we
are going to kill them
--Now insert the records in the Created table
INSERT INTO @SIDList
SELECT spid FROM master.dbo.sysprocesses WHERE dbid = db_id(@dbname)
DECLARE @CurrentRecord INT,
@TotalRecord INT
--Get the count of total connections
SELECT @TotalRecord = COUNT(*) FROM @ SIDList
--Set the current record as zero
SET @CurrentRecord = 0
DECLARE @SIDVar INT
WHILE(@CurrentRecord < @TotalRecord)
BEGIN
--Increment the
counter
SET @CurrentRecord =
@CurrentRecord + 1
SELECT @SIDVar = SID FROM @SIDList WHERE ID = @CurrentRecord
--Now, we got the ID to kill the process
EXECUTE(‘KILL’ + CAST(@SIDVar AS VARCHAR(10))) --Kill the
connection
Conclusion
So, this way we can remove the error of database already in use.