Kill SQL process before Restoring DB

There are times when you want to kill all the processes before restoring a DB. In addition you might have to create a procured because you want to use this regardless of any DB.

Here is the easy way to do this:

--------------------------------------------------------------------------------------------------------

use msdb

go

create proc [dbo].[Kill_SPID]

@DB varchar(100)

as

set nocount on

declare @sql varchar(100), @spid int

declare c cursor for

select spid from master..sysprocesses where db_name(dbid) = @DB and status <>'background'

open c fetch next from c into @spid

while @@fetch_status <> -1 begin

set @sql = 'kill ' + cast(@spid as varchar(15))

exec(@sql)

fetch next from c into @spid

end

close c

deallocate c

------------------------------------------------------------------------------------------------------------

Now all you have to do is call the SP with the database parameter and kill all the processes. 

i.e                exec Kill_SPID    my_database