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?

force exit

Hi,

I have a package that goes out and picks up a file off of a ftp server using the ftp task. How do I force the package to stop running if the file is not there?

"to force exit" is equivalent with "no execution", so

try to use "precedence constraints" (on that green arrow): if "a_condition" is true execute ftp task else is not run anythink;

to get the information that the file exists i think it is a possibility using WMI in a ActiveX script task and testing a variable (to build "a_condition")- it is an ideea|||I have a success contraint to continue if it's there and a on failure constraint to quit, which it does but it keeps sending my on error email but there is no error? I even have the failpackageonfailure property set to False and it still keeps giving the error.|||

I got help by going to this blog, it worked perfect.

http://dichotic.wordpress.com/2006/11/01/ssis-test-for-data-files-existence/

|||

Exactly what I thaught: the blogger build the condition and use the precedence constraints !

force exit

Hi,

I have a package that goes out and picks up a file off of a ftp server using the ftp task. How do I force the package to stop running if the file is not there?

"to force exit" is equivalent with "no execution", so

try to use "precedence constraints" (on that green arrow): if "a_condition" is true execute ftp task else is not run anythink;

to get the information that the file exists i think it is a possibility using WMI in a ActiveX script task and testing a variable (to build "a_condition")- it is an ideea|||I have a success contraint to continue if it's there and a on failure constraint to quit, which it does but it keeps sending my on error email but there is no error? I even have the failpackageonfailure property set to False and it still keeps giving the error.|||

I got help by going to this blog, it worked perfect.

http://dichotic.wordpress.com/2006/11/01/ssis-test-for-data-files-existence/

|||

Exactly what I thaught: the blogger build the condition and use the precedence constraints !