kill users before restore

  • I need to performe a restore to a database everyday 2:00 in the morning which I made a job. sometimes, the job failed because of some user connections in the database.

    This is a headache. It is impossible to login into the system to kill users at that time, and "sp_dboption, 'single user', true", doesn't work also because of the user connections.

    Any ideas???

    Thanks.

  • I created a sproc that creates a cursor from sp_who2 (or you can use sysprocesses) and kills each connection.

    I usually run this before a restore a couple times. Some connections take a few seconds to kill.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • When you make a stored procedure with a cursor to kill each connection, do you pass a parameter to the kill command?

    I recently tried to do something similar but the kill command didn't seem to work with a parameter instead of a spid. (ex kill @proc_id) I didn't know how to get around that.

  • select @cmd = 'kill ' + @spid

    exec(@cmd)

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • ALTER DATABASE 'database'

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    I think this is supposed to kill all connections. Am I wrong?

  • uddim,

    That doesn't work for SQL7.

    I use this as a step before my restore step in the job that copies production to dev each morning:

     
    
    use master

    declare @i int, @spid int, @strSQL varchar(255)
    select @i = 1, @spid = 0

    SET NOCOUNT ON

    while @i <> 0 BEGIN

    select @spid = max(spid) from master..sysprocesses where dbid = (select dbid from master.dbo.sysdatabases where name = 'MY-DATABASE-NAME')
    IF @spid IS NULL or @spid < 7
    SELECT @i = 0
    else begin

    SELECT @strSQL = 'KILL ' + convert(varchar(10),@spid)
    PRINT 'Killing ' + convert(varchar(10),@spid)
    EXEC (@strSQL)

    end
    END

    Edited by - Nick Beagley on 07/04/2002 06:44:51 AM

  • I have a vbscript that does this. You would have to schedule it to run but it should do the trick. Let me know if you are interested.

    Bruce Szabo, MCSE+I, MCDBA, MCSD


    Bruce Szabo, MCSE+I, MCDBA, MCSD

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply