During a project I had to restore a database a lot to sync database with schema and data between the developers. Due to some technical issues and that we worked at different locations we could not share a common database. I always had a problem with the restore didn’t succeed because there were open connections that used the database. I had to restart the SQL server every time before restoring. This was annoying and time-consuming so I created a stored procedure (after some googling) that drops all connections.

Use script

use [master]
go

exec sp_dropallconnections N'dbname'

RESTORE DATABASE [dbname] FROM  DISK = N'C:\dbname.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Script definition

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================================================== **
**      Author: mollwe
** Create date: 2012-01-10
** Description: Drops all connection to a database. Return number of dropped 
**              connections. Doesn't allow dropping connections to null and 'master' 
**              databases. It avoids dropping current connection.
**              Uses kill, see http://msdn.microsoft.com/en-us/library/ms173730.aspx 
**              for more information about kill.
**
** Change list
**
** Date        Author		Comment 
** ----------  ------------	------------------------------------------------------
**
** =============================================================================== */
ALTER PROCEDURE [dbo].[sp_dropallconnections] 
	@dbname nvarchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @droppedConnections int
	set @droppedConnections = 0

	begin try

		if(@dbname is null) raiserror('@dbname cannot be null', 18, 0)
		if(@dbname = 'master') raiserror('@dbname cannot be ''master''', 18, 1)

		declare @connections table (
			spid int, 
			ecid int, 
			status varchar(max), 
			loginname varchar(max), 
			hostname varchar(max), 
			blk bit, 
			dbname varchar(max), 
			cmd varchar(max), 
			request_id int
		)

		insert into @connections
			exec sp_who

		declare @spid int, @sql nvarchar(max)

		declare c cursor for
			select spid from @connections where dbname = @dbname

		open c

		fetch next from c into @spid

		while @@fetch_status = 0 begin
			set @sql = N'kill ' + convert(varchar, @spid)
			print @sql
			if(@spid != @@spid) begin
				begin try
					exec sp_executesql @sql
					set @droppedConnections = @droppedConnections + 1
					print 'Done!'
				end try
				begin catch 
					print error_message()
				end catch
			end
			else begin
				print 'Failed to drop connection because it is the current connection!'
			end
			print ''
			fetch next from c into @spid
		end

		close c
		deallocate c
	end try
	begin catch
		-- Raise an error with the details of the exception
		declare @errmsg nvarchar(4000), @errseverity int, @errstate int
		select @errmsg = error_message() + case 
				when error_procedure() is null then '' 
				else '(' + error_procedure() + ', line ' + cast(error_line() as varchar(5)) + ')' 
				end, 
			@errseverity = error_severity(),
			@errstate = error_state()
		raiserror(@errmsg, @errseverity, @errstate)
		return -1
	end catch

	return @droppedConnections
END