February 2, 2007 at 9:14 am
Hi - I've got an SP for killing processes, and I'm trying to put a DTS together to kill any connections to a specific DB before restoring it from a backup.
Has anybody done this successfuly before?
Paul
February 2, 2007 at 11:42 am
Not sure why you'd want to do this in DTS, but you can execute a stored procedure from DTS by using an Execute SQL task. Since you posted this in a SQL 2005 forum, maybe you're talking about an SSIS package?
There are several "kill connection" scripts posted in the Scripts section of this site. Search for "kill all connections".
Greg
Greg
February 2, 2007 at 12:15 pm
Just do this. It will kick everyone out of the database, and they will not be able to reconnect, because it will not be available.
Alter database MyDatabase set offline with rollback immediate
February 5, 2007 at 1:38 am
Greg - I want to DTS this because I've a training database that needs to be restored each evening ready for the next day's training. I thought I'd put everything into a DTS because then I can schedule it for early morning before training starts (and I don't need to be there...)
I've actually got this working now - I've set up an SP to 'kill' all the sessions and call that first. 'On success' of that action, I use T-SQL to restore the database (with a connection to the master database).
February 5, 2007 at 2:21 pm
Paul,
Thanks for the details. I still think you could do this without DTS. You can create a job with the first step executing the "kill" stored procedure and the second step doing the restore. The second step is dependent on the first succeeding (you set the "on success/failure flow" on the advanced tab of the step in Enterprise Manager. Schedule the job and you're set.
Greg
Greg
February 6, 2007 at 3:05 am
Greg - thanks for that; I've now set this up as a job as you've advised.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply