Drop DB using MSBuild even if in use

Question

I am using the following command in my MSBuild file to drop a database

sqlcmd -E -S <ServerName> -Q "DROP DATABASE <DBName>"

But sometimes I get the error

Cannot drop database because it is currently in use.

What command should I use so that the DB is dropped even if it is in use?

Answer 1

You can set your database to SINGLE_USER to drop all existing connections.

sqlcmd -E -S <ServerName> -Q "ALTER DATABASE <DBName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
sqlcmd -E -S <ServerName> -Q "DROP DATABASE <DBName>"

This is preferred to looping over each of the connections and dropping them one by one, because some applications will immediately reconnect.

Answer 2

To kill all connections to your database. See Kill All Active Connections To A Database

Now issue your DROP DATABASE command after the above procedure.