force process termination on restore

Sorry if this is a dumb question, but we have several production databases
that get automatically backed up, Rar'd, FTP'd internally and then restored.
Problem is, if someone has a process open against the restored database (IE:
a report is being run or something) the restore fails.
There must be a way of making the restore wait until a process is finsihed
or to force a termination of any process (altho probably not a good practice
)
that is running.
Basically, I'm just looking for a better backup/restore process. Any ideas
?Hi,
There are 2 approaches...
1. If you are restoring in non production then you could disconnect all the
users using below command
ALTER database <dbname> set single_user with rollback immediate
go
Restore database
go
ALTER database <dbname> set multi_user
2. Write a loop sort to check the sysprocesses table for this particular
database for any running process . If there
is any running processes
start1:
if (select status from master..sysprocesses where status='runnable' and
db_name(dbid)='pubs')>1
waitfor delay(......) -- set 1 minutedelay
goto start1
else
kill all the users connected
restore the database
I recommend the first method for development and test server databases.
Thanks
Hari
MCDBA
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:03CD2B22-81B6-4EB1-B782-A86E2C6EF9A1@.microsoft.com...
> Sorry if this is a dumb question, but we have several production databases
> that get automatically backed up, Rar'd, FTP'd internally and then
restored.
> Problem is, if someone has a process open against the restored database
(IE:
> a report is being run or something) the restore fails.
> There must be a way of making the restore wait until a process is finsihed
> or to force a termination of any process (altho probably not a good
practice)
> that is running.
> Basically, I'm just looking for a better backup/restore process. Any
ideas?|||Jason,
I had the same problem and wrote the following code to kill the users
connected to the database when I wanted to do to restore. Would this help
you?
***************************************
use master
go
select p.spid, d.name
into #temp_who
from sysprocesses p
join sysdatabases d on
p.dbid = d.dbid
where d.name = 'PUBS'
DECLARE @.objName int, @.sql NVARCHAR(50)
SET @.objName = 0
WHILE @.objName IS NOT NULL
BEGIN
SELECT @.objName = MIN(spid)
FROM #temp_who
WHERE spid > @.objName
IF @.objName IS NOT NULL
BEGIN
--Insert code to do stuff here.
set @.sql = 'kill ' + cast(@.objName as nvarchar)
exec sp_executesql @.sql
END
END
Good luck.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:03CD2B22-81B6-4EB1-B782-A86E2C6EF9A1@.microsoft.com...
> Sorry if this is a dumb question, but we have several production databases
> that get automatically backed up, Rar'd, FTP'd internally and then
restored.
> Problem is, if someone has a process open against the restored database
(IE:
> a report is being run or something) the restore fails.
> There must be a way of making the restore wait until a process is finsihed
> or to force a termination of any process (altho probably not a good
practice)
> that is running.
> Basically, I'm just looking for a better backup/restore process. Any
ideas?