Need script to kill all processes on a database

I restore a backup every week. It serves a couple of purposes. One, to verify the backup and secondly, to give us a current testing/training environment. As things evolve, it would be nice to script this and then schedule it as a job.

I can write the restore statement, but it fails if any sessions are open on the db. I thought about using sp_who and the the kill statement. But I was having trouble using the recordset returned by sp_who.

Now I'm thinking the sysprocess table is a better place to look. Does anybody have some experience in this area and can point me in the right direction?

While I'm going down this path, does anybody have a script to look at the master database and dynamically manage their backup strategy? That would be super cool?!?!

Thanks,

Alex8675I restore a backup every week. It serves a couple of purposes. One, to verify the backup and secondly, to give us a current testing/training environment. As things evolve, it would be nice to script this and then schedule it as a job.

I can write the restore statement, but it fails if any sessions are open on the db. I thought about using sp_who and the the kill statement. But I was having trouble using the recordset returned by sp_who.

Now I'm thinking the sysprocess table is a better place to look. Does anybody have some experience in this area and can point me in the right direction?

Go to google type "kill all processes IN A DATABASE SQL SERVER" i got atleast 10 different SP's which will do your job.....choose the one which suits you|||this will work if you are not logged connected to the database and no other sysadmin is doing anything...

ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE|||Very elegant solution and exactly what I was looking for...but I couldn't find and was going a long way around to find what was readily available.|||this will work if you are not logged connected to the database and no other sysadmin is doing anything...

ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
This one's not as elegant, but worked when I had to disconnect / move the log files tonight.
DECLARE @.sql nVARCHAR(4000)
SET @.sql = ''

-- To disconnect, first kill all processes.
SELECT @.sql = @.sql + ' KILL ' + CAST(spid AS VARCHAR(10)) + ' '
FROM master.dbo.sysprocesses
WHERE DB_NAME(dbid) = @.MYDB
AND spid > 50 AND spid <> @.@.SPID
EXEC(@.sql)

Thrasymachus; Do I have to do something to undo the SET?