SQL Server : To drain all the connections for a particular database

/***********************************************************************
Kill all the spid's attached to a particular database. Very useful when 
continually restoring backups for testing.

***********************************************************************/
Error Msg:

ALTER DATABASE failed because a lock could not be placed on database 'employee'. 

ALTER DATABASE statement failed.
Cannot drop database "employee" because it is currently in use.

Solution: 

To drain all the connections , use bellow SQL to kill all the connections.

use master
go
Sp_killall 'employee'

to verify, use following mentioned code

select * from sys.sysprocesses where dbid = <dbid for your database>

Note: Create this (sp_KillAll Stored Procedure) in the master database, and call it from anywhere; but you can't kill your own process!

*********************Script to create sp_KillAll Stored Procedure *****************

CREATE PROCEDURE sp_KillAll
@DbName VARCHAR(100)
AS


IF db_id(@DbName) = NULL 
BEGIN
    PRINT 'DataBase dose not Exist'
END
ELSE
BEGIN
    DECLARE @spId VARCHAR(30)
        
    DECLARE TmpCursor CURSOR FOR
    SELECT  'Kill ' + CONVERT(VARCHAR, spid)  AS spId
      FROM master..SysProcesses
      WHERE db_Name(dbID) = @DbName
      AND spId <> @@SpId
      AND dbID <> 0
      
    OPEN TmpCursor
        
    FETCH NEXT FROM TmpCursor INTO @spId 
        
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC (@spId)
     
        FETCH NEXT FROM TmpCursor INTO @spId 
    END

    CLOSE TmpCursor
    DEALLOCATE TmpCursor
END 
GO 

Warm Regards,
Chhavinath Mishra 
Sr. Specialist Database Administrator