SQL-Server: Error - Exclusive access could not be obtained because the database is in use

Question

1. Set the path to restore the file.
2. Click "Options" on the left hand side.
3. Uncheck "Take tail-log backup before restoring"
4. Tick the check box - "Close existing connections to destination database".
5. Click OK.

I'll assume that if you're restoring a db, you don't care about any existing transactions on that db. Right? If so, this should work for you:

USE master
GO

ALTER DATABASE AdventureWorksDW
SET SINGLE_USER
--This rolls back all uncommitted transactions in the db.
WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE AdventureWorksDW
FROM ...
...
GO

Now, one additional item to be aware. After you set the db into single user mode, someone else may attempt to connect to the db. If they succeed, you won't be able to proceed with your restore. It's a race! My suggestion is to run all three statements at once.

execute this query before restoring database:

alter database [YourDBName] 
set offline with rollback immediate

and this one after restoring:

  alter database [YourDBName] 
  set online

For me, the solution is:

1. Check Overwrite the existing database(WITH REPLACE) in optoins tab at left hand side.

2. Uncheck all other options.

3. Select source and destination database.

4. Click ok.

That's it.

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

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...'

Hope this will help...

taking original db to offline worked for me

I just restarted the sqlexpress service and then the restore completed fine