Restore Database Error while DB in use or MDF not found Error

Exclusive access could not be obtained because the database is in use  Resolved

Tweet

Sometimes this is a common error message that we encounter, when we try to restore a SQL database, which is being used by other users.

This can occur due to various reasons. But the most common incident is, users not closing the Management Studio’s query window after they have finished the query task.

There are few ways of resolving this and restore the database.

1. Find all the active connections, kill them all and restore the database.
2. Get database to offline (And this will close all the opened connections to this database), bring it back to online and restore the database.

Method 1

Use the following script to find and kill all the opened connections to the database before restoring database.

 Collapse | Copy Code

declare @sql as varchar(20), @spid as int
select @spid = min(spid)  from master..sysprocesses  where dbid = db_id('<database_name>') 
and spid != @@spid    

while (@spid is not null)
begin
    print 'Killing process ' + cast(@spid as varchar) + ' ...'
    set @sql = 'kill ' + cast(@spid as varchar)
    exec (@sql)

    select 
        @spid = min(spid)  
    from 
        master..sysprocesses  
    where 
        dbid = db_id('<database_name>') 
        and spid != @@spid
end 

print 'Process completed...'

Method 2

Use the following code to take database offline and bring back to online so that all the active connections will be closed. And afterwards restore the database.

 Collapse | Copy Code

alter database database_name<br>set offline with rollback immediate
alter database database_name
set online
go

Method 3Setting the database to single user mode and later, setting to multi user will also close all the active connections. And this method is faster than the 'Method 2'.

use master
go
alter database <dbname>
set single_user with rollback immediate
go
alter database <dbname>
set multi_user
go

DB MDF or LDF not found Error:

When You Create new db and choose .bak file of source db and click 

 Restore DB Source db following error came as see below:

step by step see below:

To resolve this error:

Click options tab and click ellipses tab see below and choose already created mdf and ldf via