SQL Server Kill Command

I have an SQL script w开发者_JS百科hich backs up a database and then restores it over another database. the problem I'm having is that the database being overwritten is being locked open by a user so the job fails. I can manually kill the attached user and it runs fine but I need this process to run automatically every night. Is there a kill command which I can time to execute every night? or is there something in the restore options to do the same thing?

Any thoughts?

Thanks

---

KILL isn't always effective if the client reconnects

I'd consider taking the database offline (or dbo only) then restoring. Thus will prevent further reconnects.

ALTER DATABASE TargetDB SET OFFLINE WITH ROLLBACK IMMEDIATE 

or

ALTER DATABASE TargetDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE 

---

agree with @gbn you can use following code to get database restore success also

USE master 
GO

ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE YourDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE 
GO

however you can use following script to kill connections to selected database.

    -- Create the sql to kill the active database connections  
DECLARE @execSql VARCHAR(4000),
    @databaseName VARCHAR(100)  
    -- Set the database name for which to kill the connections  
SET @databaseName = '[yourdatabase]'  

SET @execSql = ''   
SELECT  @execSql = @execSql + 'kill ' + CONVERT(CHAR(10), spid) + ' '
FROM    master.dbo.sysprocesses
WHERE   DB_NAME(dbid) = @databaseName
        AND DBID <> 0
        AND spid <> @@spid  
EXEC ( @execSql
    )  

---

DECLARE @pid AS INTEGER

DECLARE mycursor CURSOR FOR
select spid 
from sys.sysprocesses
WHERE dbid = DB_ID('yourdatabasename')

OPEN mycursor 
FETCH NEXT FROM mycursor INTO @pid

WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE ('KILL '+@pid)
FETCH NEXT FROM mycursor INTO @pid
END

CLOSE mycursor
DEALLOCATE mycursor

---

Select 'Kill '+ CAST(p.spid AS VARCHAR)KillCommand into #temp
from master.dbo.sysprocesses p (nolock)
join master..sysdatabases d (nolock) on p.dbid = d.dbid
Where d.[name] = 'your db name'

Declare @query nvarchar(max)
--Select * from #temp
Select @query =STUFF((
            select '  ' + KillCommand from #temp
            FOR XML PATH('')),1,1,'')
Execute sp_executesql @query
Drop table #temp

Use the ‘master’ database and run this query, it will kill all the active connections from your database.