How to kill all database connections from your SQL Server restore database script.

So when I'm building an app, especially before launch, I like to have scripts that completely drop and rebuilds my local, local test, and production databases from the relevant DDL files, then add data where needed.

Then I write .bat scripts so I can invoke the same set of scripts for each environment.

This works great except sometimes the DROP DATABASE statement fails due to a lingering connection:

Msg 3702, Level 16, State 4, Server SOURCECRAFT-PC\LOCALDB#C12079B7, Line 13
Cannot drop database "databasename" because it is currently in use.
Msg 1801, Level 16, State 3, Server SOURCECRAFT-PC\LOCALDB#C12079B7, Line 2
Database 'databasename' already exists. Choose a different database name.

Sometimes, maddeningly, you have to close your server connection from SQL Server Management Studio and exit Visual Studio 2012 before whatever connection will let go - which is not terribly productive.

The solution is to add this before your DROP DATABASE script. It quietly kills any lingering connection you might have, allowing your script to work as it should every time, and crucially allowing you to leave Visual Studio 2012 and SQL Server Management Studio open while you drop and recreate your database.

USE [master]
GO
-- this is to kill any connections lingering, like Visual Studio
ALTER DATABASE [databasename]
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [databasename]
SET ONLINE
GO
-- real script begins here...
DROP DATABASE [databasename]
GO

Hope that helps you out just a little.