Kill Sql Server Sessions , set to OFFLINE or SINGLE_USER

1. Kill Sql Server Sessions 

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'Database_Name' 

DECLARE @SQL varchar(max) SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId --Use this to see results SELECT @SQL --Uncomment this to run it --EXEC(@SQL)

2. You can turn your database to OFFLINE mode , which will inturn kill all the active connection.

  So your step will be something like 

  - Bring your database to offline mode

  - Bring it Online

  - Run your queries, like restore.

ALTER DATABASE [Database_Name] SET OFFLINE WITH ROLLBACK IMMEDIATE 

3. Another option is to set your database to single user mode and then try to run your query. 

  As soon as you make this to single user mode, it kills all the active connections.

  So your step will be something like 

  a) set to single user mode with roll back

  b) run your queries like restore etc.

  c) set it back to multi user mode.

 

use master
    ALTER DATABASE 'Database Name' SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
    --do you stuff here 
    ALTER DATABASE 'Database Name' SET MULTI_USER

 

ALTER DATABASE [Database Name] SET SINGLE_USER WITH NO_WAIT
ALTER DATABASE [Database Name] SET MULTI_USER WITH  NO_WAIT

OR

EXEC sp_dboption [Database Name], 'single user', 'false' 

EXEC sp_dboption [Database Name], 'single user', 'true'

两者区别在于：sp_dboption  在SQL SERVER 2005后可能 将不支持