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

I am actually trying to make a script (in Sql Server 2008) to restore one database from one backup file. I made the following code and I am getting an error -

Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because
the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

How do I fix this problem ?

IF DB_ID('AdventureWorksDW') IS NOT NULL
BEGIN
RESTORE DATABASE [AdventureWorksDW]
FILE = N'AdventureWorksDW_Data'
FROM
DISK = N'C:\Program Files\Microsoft SQL Server\
MSSQL10_50.SS2008\MSSQL\Backup\AdventureWorksDW.bak'
WITH FILE = 1,
MOVE N'AdventureWorksDW_Data'
TO N'C:\Program Files\Microsoft SQL Server\
MSSQL10_50.SS2008\MSSQL\DATA\AdventureWorksDW.mdf',
MOVE N'AdventureWorksDW_Log'
TO N'C:\Program Files\Microsoft SQL Server\
MSSQL10_50.SS2008\MSSQL\DATA\AdventureWorksDW_0.LDF',
NOUNLOAD, STATS = 10
END

I think you just need to set the db to single user mode before attempting to restore, like below, just make sure you're using `master`

USE master
GO
ALTER DATABASE AdventureWorksDW
SET SINGLE_USER

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.

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

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.

Here's a way I am doing database restore from production to development:

NOTE: I am doing it via SSAS job to push production database to development daily:

Step1: Delete previous day backup in development:

declare @sql varchar(1024);

set @sql = 'DEL C:\ProdAEandAEXdataBACKUP\AE11.bak'
exec master..xp_cmdshell @sql

Step2: Copy production database to development:

declare @cmdstring varchar(1000)
set @cmdstring = 'copy \\Share\SQLDBBackup\AE11.bak C:\ProdAEandAEXdataBACKUP'
exec master..xp_cmdshell @cmdstring

Step3: Restore by running .sql script

SQLCMD -E -S dev-erpdata1 -b -i "C:\ProdAEandAEXdataBACKUP\AE11_Restore.sql"

Code that is within AE11_Restore.sql file:

RESTORE DATABASE AE11
FROM DISK = N'C:\ProdAEandAEXdataBACKUP\AE11.bak'
WITH MOVE 'AE11' TO 'E:\SQL_DATA\AE11.mdf',
MOVE 'AE11_log' TO 'D:\SQL_LOGS\AE11.ldf',
RECOVERY;

Use Master
alter database databasename set offline with rollback immediate;

--Do Actual Restore
RESTORE DATABASE databasename
FROM DISK = 'path of bak file'
WITH MOVE 'datafile_data' TO 'D:\newDATA\data.mdf',
MOVE 'logfile_Log' TO 'D:\newDATA\DATA_log.ldf',replace

alter database databasename set online with rollback immediate;
GO

I got this error when there was not enough disk space to restore Db. Cleaning some space solved it.

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