Question: I have an SQL script which 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

Answer: 
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 

```
```
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.