I wanted to figure out how (if possible) I could possibly kill all connections to a specific database and then take it offline. So what I did was create the following SQL script:

First I created a temporary table to use for storing the current connection info. The columns indicated in this table are the columns returned by the sp_who2 command.

CREATE TABLE #temp
(
    SPID1 int,
    [Status] varchar(500),
    [Login] varchar(500),
    HostName varchar(500),
    BlkBy varchar(500),
    DBName varchar(500),
    Command varchar(500),
    CPUTime int,
    DiskIO int,
    LastBatch varchar(500),
    ProgramName varchar(500),
    SPID2 int,
    REQURESTID int
)

Next I wanted to populate the #temp table I'd just created with the relevant connection data, so I ran:

INSERT INTO #temp
EXEC sp_who2

You can now select from the #temp table and you will see all the connection info at the time of the INSERT command above. So now to kill all the connections to a particular database, I created and ran the following script:

DECLARE
    @SPID int,
    @KillCmd varchar(150)

--change the My_Database_Name to your database name
DECLARE myCursor CURSOR FOR
SELECT SPID1 FROM #temp
WHERE DBName = 'My_Database_Name'

OPEN myCursor

FETCH NEXT FROM myCursor INTO @SPID

WHILE @@fetch_status <> -1
BEGIN
    SET @KillCmd = 'kill ' + CONVERT(varchar(50), @SPID)
    EXEC (@KillCmd)
    FETCH NEXT FROM myCursor INTO @SPID
END

CLOSE myCursor
DEALLOCATE myCursor

You will notice that I select the first SPID from the #temp table for my particular database. All you have to do is insert your particular database name in that portion of the script, run it and it will kill all the connections to that database. I have however notcied that some connections take a little time to die, not sure why this is, but I definitely found this script very useful.

Happy scripting!

Open SQL Server management studio, and right click on the instance name and select "Properties". In the properties window, select the "Database settings" page and you will see "Database default locations" on the window displayed. Change the folders, click "Ok".

It's that easy...