terminate all connections using transact-sql 1

[password99]

is there a way to terminate all connections using transact-sql (before performing restore opertaion)

 

[password99]

or a way to get a forced exclusive lock on the DB using Transact-SQL

 

[mrdenny]

You can kill all the connections in the database by using a cursor. Something like this.

declare @spid varchar(10)
declare @dbname varchar(100)
set @dbname = 'DatabaseName'
declare cur CURSOR for select spid from master.dbo.sysprocesses where dbid = (select dbid from master.dbo.sysdatabases where name = @dbname)
open cur
fetch next from cur into @spid
while @@fetch_status = 0
begin
    exec ('kill ' + @spid)
    fetch next from cur into @spid
end
close cur
deallocate cur
go
restore database ...
go

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)