SQL Server restore database with active connections [duplicate]

Question

I am trying to run this SQL script on a SQL Server instance to restore a test database to a newer copy of the live database.

RESTORE FILELISTONLY
   FROM disk = '\\staging_server\path\to\db_backup.bak'    
-- Restore the files for 
RESTORE DATABASE Test_DB
   FROM disk = '\\staging_server\path\to\db_backup.bak'
   WITH replace,
   MOVE 'Test_DB' TO 'D:\R2_BIN\Test_DB.mdf', 
   MOVE 'Test_DB_log' TO 'D:\R2_BIN\Test_DB_log.ldf', 
   stats = 5
GO

However, on running the script I receive the following error.

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

Is there some way I can force this operation to go through, and kill any connections it needs to to get it done? I tried to close down any applications that may connections to the DB, but some of the connections I simply cannot identify. There are several from my username and yet I only have one instance of SQL Server Management Studio open.

Anyway, is there some way to kill all connections to a specific database? There are quite a few databases running on this instance, so I don't want to just shutdown and restart the instance all together if I can help it.

Answer 1

You must switch to single user mode:

-- Close all connections and rollback all transaction
ALTER DATABASE Test_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

RESTORE FILELISTONLY
   FROM disk = '\\staging_server\path\to\db_backup.bak'    
-- Restore the files for 
RESTORE DATABASE Test_DB
   FROM disk = '\\staging_server\path\to\db_backup.bak'
   WITH replace,
   MOVE 'Test_DB' TO 'D:\R2_BIN\Test_DB.mdf', 
   MOVE 'Test_DB_log' TO 'D:\R2_BIN\Test_DB_log.ldf', 
   stats = 5
GO

ALTER DATABASE Test_DB SET MULTI_USER;
GO

Answer 2

Msg 3101, Level 16, State 1, Line 5 Exclusive access could not be obtained because the database is in use.

The error message says it all. Restore operation requires exclusive access on database when you are trying to restore it from backup. Since it is not able to get you are getting error message.

Since you are restoring on already present database you dont need to use WITH MOVE command. Below should work

use master
go
alter database Test_DB set single_user with rollback immediate
go
RESTORE DATABASE Test_DB
   FROM disk = '\\staging_server\path\to\db_backup.bak'
   WITH replace
   go

Answer 3

Killing the sessions is something that should not be done in very first place:

The approach which i use while doing a restore during such refresh activity would be:

Approach 1:

Bring the database in single user mode, perform the restore and reset back to multi_user

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

Do the restore and set back to multi_user

alter database <database_name>
set multi_user
go

Approach 2:

If above does not work: Bring the database offline and back online with below:

-- Take the Database Offline

ALTER DATABASE [database name] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
-- Take the Database Online
ALTER DATABASE [myDB] SET ONLINE
GO

Approach 3: Not the best way but do as per you're needs

See the open sessions of the database using SP_who2 or SP_whoisactive and kill the sessions which you analyse should be without any impact.

or use below script to kill all open sessions for that 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...'